Join tables ON A ∩ B conditions

Question

I wish to join two (or more) tables based on a set of columns that are present in all tables that take part in the join. In other words I wish to join tables based on the intersection set of columns. However each table has extra columns which are unique to that table.

Intersection set

Example

#: A 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've come up with a solution but the performance is horrid, performance is an issue. I don't want to pollute you with that solution. I'd much rather have a fresh set of eyes on this :)

Looking forward to seeing your solutions. Thank you for your time. It's much appreciated.

UPDATE

Thank you for all the responds. However it seems I didn't explain the problem well enough. (Haven't tested all answers yet)

But note how Table B has a row that's not present in Table A.

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

And Table A vice versa.

The solution I've come up with unions all tables together on the intersection set of columns to generate a skeleton.

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

Once I have the 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 query 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

If you want T-SQL you're either going to have to write it yourself or generate it with custom code. — bluevector, Jun 12 '12 at 19:43
is this a static query, or a dynamic one (you give 5 tables, you wanna find "common columns" and generate the query ?) — Raphaël Althaus, Jun 12 '12 at 19:46
@RaphaëlAlthaus it would be a static query. I know beforehand what tables I need to join and what columns is part of the intersection set — Snæbjørn, Jun 12 '12 at 19:49

score 3 · Accepted Answer · answered Jun 12 '12 at 19:43

3

Just the thing for a 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

answered Jun 12 '12 at 19:43

Andomar

232,371
49
380
404

2

He might be interested in a cross product, but he stated "on the intersection set of columns" which implies structure, not data. – bluevector Jun 12 '12 at 19:45
Yeah, it sounded to me like an `INNER JOIN`, just with the column list you provided. – Dominic P Jun 12 '12 at 19:46
This answer is based on the example output, which contains rows from A not matched in B. – Andomar Jun 12 '12 at 19:48
Sorry for the delay. This answer is the fastest query and does what I need :) - Many thanks – Snæbjørn Jun 15 '12 at 10:07

score 0 · Answer 2 · edited May 23 '17 at 12:19

0

This might help:

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

Or take a look here:

Combine two tables for one output

edited May 23 '17 at 12:19

Community

1
1

answered Jun 12 '12 at 19:50

Valentin Vrinceanu

679
1
12
24

I would be suprised that a query on 3 column could give a 4 column result as asked by OP. – Raphaël Althaus Jun 12 '12 at 20:15

score 0 · Answer 3 · answered Jun 12 '12 at 19:52

You can resolve a double outer join with a 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

HABO · Answer 4 · 2012-06-12T23:32:33.440

Oh my. Be wishful what you care for.

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

Join tables ON A ∩ B conditions

Example

UPDATE

4 Answers4