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.

Zolner

Edit your question and show the whole query. – Worry

Please in code questions give a minimal reproducible example--cut & paste & runnable code plus desired output plus clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) – Let

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

Medallion

This doesn't appear to be well documented but I have seen the same issue - a left outer join will not necessarily return all rows from the left hand table if the join condition columns contain NULL values. Can be resolved adding an ISNULL() around each of the columns. – Wonderful

Great answer, odd that Redshift doesn't handle this intrinsically, but NULL behavior always seems to work differently from other values. – Robi

This behavior is still seen on Redshift Postgres 12.X as of 2023. – Shockley

Answer 2

It seems that as of today, everything works as expected

WITH tst1 AS ( SELECT 'A' AS col1
                    , 'C' AS col2
                    , 'E' AS col3
            UNION ALL
               SELECT 'A' AS col1
                    , 'D' AS col2
                    , 'F' AS col3 )
                    
   , tst2 AS ( SELECT 'A'::VARCHAR AS col1
                    , 'C'::VARCHAR AS col2
                    , 'Z'::VARCHAR AS col3 )
                    
SELECT tst1.col1
     , tst1.col2
     , tst1.col3
     , tst2.col3 AS col4
FROM tst1
    LEFT JOIN tst2 ON tst1.col1 = tst2.col1
                  AND tst1.col2 = tst2.col2;

col1	col2	col3	col4
A	C	E	Z
A	D	F

Globate

Answer 3

I think outer keyword is missing in your query. Here is an example of left outer join.

=> select * from Table1;
 col1 | col2 | col3
------+------+------
 A    | D    | F
 A    | C    | E
(2 rows)

=> select * from Table2;
 col1 | col2 | col4
------+------+------
 A    | C    | Z
(1 row)

=> select Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4 from Table1 left outer join Table2 on Table1.Col1 = Table2.Col1 and Table1.Col2 = Table2.Col2;
 col1 | col2 | col3 | col4
------+------+------+------
 A    | D    | F    |
 A    | C    | E    | Z
(2 rows)

Hoarse

The OUTER keyword is optional in left or right joins. – Wonderful