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
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
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)
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
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