Join tables ON A ∩ B conditions

Question

I want to join two (or more) tables based on the set of columns that are present in all the tables involved in the join. In other words, I want to join tables based on a set of column intersections. However, each table has additional columns that are unique to this table.

Example

#: number
-: NULL

Table a

+------+------+------+ | Col1 | Col2 | ColA | +------+------+------+ | A | A | # | | A | B | # | +------+------+------+ 

Table B

 +------+------+------+ | Col1 | Col2 | ColB | +------+------+------+ | A | A | # | | B | B | # | +------+------+------+ 

Result

 +------+------+------+------+ | Col1 | Col2 | ColA | ColB | +------+------+------+------+ | A | A | # | # | | A | B | # | - | | B | B | - | # | +------+------+------+------+ 

I came up with a solution, but the performance is terrible, performance is the problem. I do not want to pollute you with this decision. I would rather have a fresh look :)

Looking forward to your decisions. Thank you for your time. It is very much appreciated.

UPDATE

Thanks for all the answers. However, it seems that I have not sufficiently explained the problem. (I have not checked all the answers yet)

But note that in table B there is a row that is not in table A.

 Table B +------+------+------+ | Col1 | Col2 | ColB | +------+------+------+ | B | B | # | +------+------+------+ 

And table A is the opposite.

The solution I developed joins all the tables together in a set of columns to create a skeleton.

 Skeleton: SELECT Col1, Col2 FROM TableA UNION SELECT Col1, Col2 FROM TableB 

As soon as I have a skeleton I LEFT OUTER JOIN for each table.

 LEFT OUTER JOIN TableA AS a ON a.Col1=skeleton.Col1 AND a.Col2=skeleton.Col2 LEFT OUTER JOIN TableB AS b ON b.Col1=skeleton.Col1 AND b.Col2=skeleton.Col2 

So the final request looks like this:

 SELECT s.*, a.ColA, b.ColB FROM ( SELECT Col1, Col2 FROM TableA UNION SELECT Col1, Col2 FROM TableB ) s LEFT OUTER JOIN TableA a ON a.Col1=s.Col1 AND a.Col2=s.Col2 LEFT OUTER JOIN TableB b ON b.Col1=s.Col1 AND b.Col2=s.Col2 

Answer 1

Just for full outer join :

 select coalesce(a.Col1, b.Col1) as Col1 . coalesce(a.Col2, b.Col2) as Col2 , a.ColA , b.ColB from A a full outer join B b on a.Col1 = b.Col1 and a.COl2 = b.Col2 

Answer 2

This can help:

 SELECT Col1, Col2, ColA FROM A GROUP BY Col1, Col2, ColA UNION ALL SELECT Col1, Col2, ColB FROM B GROUP BY ColB 

Or look here:

Combine two tables for one output

Answer 3

You can enable double outer join with Union:

 SELECT TableA.Col1,TableA.Col2, TableA.ColA,TableB.ColB FROM TableA LEFT JOIN TableB ON TableA.Col1=TableB.Col1 AND TableA.Col2=TableB.Col2 UNION SELECT TableB.Col1,TableB.Col2, TableA.ColA,TableB.ColB FROM TableB LEFT JOIN TableA ON TableA.Col1=TableB.Col1 AND TableA.Col2=TableB.Col2 

Answer 4

Oh, my. Get ready for what you need.

 -- Sample data. declare @TableA as Table ( Col1 VarChar(8), Col2 VarChar(8), ColA Int NULL ) insert into @TableA ( Col1, Col2, ColA ) values ( 'A', 'A', 42 ), ( 'A', 'B', 7 ) declare @TableB as Table ( Col1 VarChar(8), Col2 VarChar(8), ColB Int NULL ) insert into @TableB ( Col1, Col2, ColB ) values ( 'A', 'A', 999 ), ( 'B', 'B', -1 ) select * from @TableA select * from @TableB -- The query. Use the aggregate(s) of your choice: SUM, MIN, MAX, AVG. select Col1, Col2, Sum( ColA ), Sum( ColB ) from ( select A.Col1, A.Col2, A.ColA, Cast( NULL as Int ) as [ColB] from @TableA as A union all select B.Col1, B.Col2, Cast( NULL as Int ), B.ColB from @TableB as B ) as Edgar group by Col1, Col2