Redshift left outer join is leaving out nulls

Question

As background, I've set up 100's of redshift query's, many much more complex than this and yet I think I must be missing something simple.

I am doing a left outer join between Table 1 and Table 2. The tables are essentially this:

Table1
Col1  Col2 Col3
A     C    E
A     D    F

Table2
Col 1 Col2 Col3
A     C    Z

I have no where statements. My on statement is:

on Table1.Col1 = Table2.Col1 and Table1.Col2 = Table2.Col2

My result table is:

ResultTable:
Col1 Col2 Col3 Col4
A    C    E    Z

I was expecting:

ExpectedTable:
Col1 Col2 Col3 Col4
A    C    E    Z
A    D    F    Null

What am I missing? Thanks.

Answer 1

This may not be your problem, but Redshift doesn't match records using the equality operator if any of the columns used in the join are null, even if the column value is null on both sides of the join.

To handle this add an or condition for each join checking if both sides are null.

e.g. ((a.col1 = b.col1) or (a.col1 is null and b.col1 is null))