What happens in a dual LEFT OUTER join when the second join is NULL in both tables?

1

Hello

Sorry for such a basic question, but what will be the output of the below LEFT JOIN?
I want to left outer join the first table with the second ON Col1 and Col2.
My question is, if there is a Col1 match but both Col2 are NULL, will these records be joined?

Col1 Col2
A 1
B
C 2
D 3
Col1 Col2
A 1
B
C 4
D 3

Thanks!

2

If your join is something like

FROM
   Table1 as t1
   LEFT JOIN Table2 as t2 ON
      t1.Col1 = t2.Col1 and t1.Col2 = t2.Col2

and if col2 in either or both tables is null for a matching value of col1, that row for Table2 will not appear in the result. Instead, you will see nulls for all columns of Table2. This is a result of the logical expression NULL = NULL being unknown (neither true, nor false).

1 Like
3

The results of:

SELECT *
FROM table1 A
LEFT OUTER JOIN table2 B ON B.Col1 = A.Col1 AND B.Col2 = A.Col2

Would be:

Col1 Col2 Col1 Col2
A 1 A 1
B B
C 2 NULL NULL
D 3 D 3

For AND JOINs, all conditions must be true for the JOIN to work.

Edit: Columns not found because of a LEFT JOIN are automatically set to NULL by SQL Server.

1 Like