CODEWITHSUNDEEP
sql-servert-sql
jedu
jedu

Reputation: 1341

T-SQL left join not returning null columns

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

Upvotes: 0

Views: 92

Answers (1)

LukStorms
LukStorms

Reputation: 29647

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.

Upvotes: 3

Related Questions