Suppose I had the following 2 tables:
Table1: Table2:
Col1: Col2: Col3: Col1: Col2: Col4:
a b c a b d
e <null> f e <null> g
h i j h i k
l <null> m l <null> n
o <null> p o <null> q
Now, I want to join these tables on Col1 and Col2 and bring back the entire set to look like:
Result:
Col1: Col2: Col3: Col4:
a b c d
e <null> f g
h i j k
l <null> m n
o <null> p q
So, I tried a SQL like:
SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1
INNER JOIN Table2
ON Table1.Col1 = Table2.Col1
AND Table1.Col2 = Table2.Col2
But it isn't matching the NULL values in Col2, so I end up with:
Result:
Col1: Col2: Col3: Col4:
a b c d
h i j k
How can I get the result I am looking for??
Thanks!
You can be explicit about the joins:
SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1 INNER JOIN
Table2
ON (Table1.Col1 = Table2.Col1 or Table1.Col1 is NULL and Table2.Col1 is NULL) AND
(Table1.Col2 = Table2.Col2 or Table1.Col2 is NULL and Table2.Col2 is NULL)
In practice, I would be more likely to use coalesce() in the join condition:
SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1 INNER JOIN
Table2
ON (coalesce(Table1.Col1, '') = coalesce(Table2.Col1, '')) AND
(coalesce(Table1.Col2, '') = coalesce(Table2.Col2, ''))
Where '' would be a value not in either of the tables.
Just a word of caution. In most databases, using any of these constructs prevents the use of indexes.