T-SQL left join not returning null columns

Question

I have 2 tables in T-SQL one with 10 records and one with 11.

select tbl.unit, tbl2.unid 
from tbl1
left join tbl2 on tbl2.UNID = tbl1.UNIT
where tbl2.Status = 'Main'
group by unit, UNID

This only returns 10 records when tbl1 has 11 records. I was expecting the missing record to show a value if UNIT but null for UNID but is just is not there.

I cannot figure out why this is the case

Well 'Main' doesn't equal NULL, so why would you expect that record to return in your results? — dfundako, Nov 25 '19 at 19:15
Does this answer your question? [Why is my t-sql left join not working?](https://stackoverflow.com/questions/40093809/why-is-my-t-sql-left-join-not-working) (Can't gold badge as I added the tag.) — Thom A, Nov 25 '19 at 19:37
Per dfundako and lamu, above: SELECT tbl.unit , tbl2.unid FROM tbl1 LEFT JOIN tbl2 ON tbl2.UNID = tbl1.UNIT AND tbl2.STATUS = 'Main' GROUP BY unit , UNID; — Russell Fox, Nov 25 '19 at 19:40
As stated, you can move `tbl2.Status = 'Main'` to your `ON` or you can add `WHERE (tbl2.Status = 'Main' OR tbl2.Status IS NULL)` — BJones, Nov 25 '19 at 19:57

LukStorms · Accepted Answer · 2020-02-21T14:46:12.593

So why would a LEFT JOIN not show all the records from a left side of the join.

Is a bug?

Most likely not.

Lets look at a simplified example.

TableA has 3 records.

ID    ColA
1     Foo
2     Bar
3     Buzz

TableB has 2 records

ID    ColB
4     Foo
5     Bar

An INNER JOIN on ColA & ColB would return 2 records.
Only those where a match is found.

SELECT ColA, ColB 
FROM TableA a
JOIN TableB b ON b.ColB = a.ColA

Returns:

ColA    ColB
Foo     Foo
Bar     Bar

A LEFT JOIN would return 3 records.
With a NULL on the right side for the unmatched.

SELECT ColA, ColB 
FROM TableA a
LEFT JOIN TableB b ON b.ColB = a.ColA

Returns:

ColA    ColB
Foo     Foo
Bar     Bar
Buzz    null

But what happens if a criteria is used in the WHERE clause for the right side?

SELECT ColA, ColB 
FROM TableA a
LEFT JOIN TableB b ON b.ColB = a.ColA
WHERE b.ColB IN ('Foo', 'Bar', 'Buzz')

Returns:

ColA    ColB
Foo     Foo
Bar     Bar

What? Where's the 'Buzz'?

Can you guess why that LEFT JOIN seems to behave like an INNER JOIN?

The solution is to put such criteria in the ON clause.

SELECT ColA, ColB 
FROM TableA a
LEFT JOIN TableB b 
   ON b.ColB = a.ColA AND b.ColB IN ('Foo', 'Bar', 'Buzz')

Or do put the criteria in the WHERE, but also allow NULL.

SELECT ColA, ColB 
FROM TableA a
LEFT JOIN TableB b 
   ON b.ColB = a.ColA
WHERE (b.ColB IN ('Foo', 'Bar', 'Buzz') 
       OR b.ColB IS NULL)

Returns:

ColA    ColB
Foo     Foo
Bar     Bar
Buzz    null

Now the Buzz is back.

T-SQL left join not returning null columns

1 Answers1

Linked