user1742188 user1742188 - 19 days ago 10
SQL Question

Join on any of several columns

I have a few tables, each containing different columns, which contain different subsets of all the products.
I want to get a list of all the information about all the products, so I want to do a

full outer join
on the
product_id
.

I tried

select * from table1
full outer join table2 b on b.product_id = table1.product_id
...
full outer join tableN c on b.product_id = table1.product_id


but this results in multiple rows where a
product_id
does not exist in table1, but might exist in table2 and tableN.
Is there a way to "coalesce" the join column?

Answer

If you use full outer join with more than two tables, then you will end up with something like this:

select *
from table1 a full outer join
     table2 b
     on b.product_id = a.product_id 
     ... full outer join
     tableN c
     on c.product_id = coalesce(b.product_id, a.product_id)

The using clause -- supported by Postgres but not SQL Server -- simplifies this. It does assume that the columns all have the same name.

An alternative is a driving table. If you don't have a table of all products handy, you can create one:

select *
from (select product_id from table1 union
      select product_id from table2 union
      . . .
      select product_id from tableN
     ) driving left join
     table1 a
     on a.product_id = driving.product_id left join
     table2 b
     on b.product_id = driving.product_id 
     ... full outer join
     tableN c
     on c.product_id = driving.product_id;

This should be easier to interpret and the on clauses are simplified.

Finally, perhaps you just want common columns. If so, just use union all:

select product_id, col1, col2, col3 from table1 union all
select product_id, col1, col2, col3 from table2 union all
      . . .
select product_id, col1, col2, col3 from tableN;

This prevents the proliferation of columns with NULL values.

Comments