CODEWITHSUNDEEP
sqlssms
Koosh
Koosh

Reputation: 896

LEFT Join does not pull record from main table if the JOINED value is null

Say I have a tableA that looks like this:

 ID         Name         IssueID
 1          Bob          2
 2          John         Null

I have my Reference table called tblRef that includes these columns:

 IssueID       IssueDesc
 2             Not Completed
 3             Completed

My query looks something like this, 

Select a.Id,a.Name, I.IssueDesc from tableA a Left Join tblRef I on a.issueId = 
I.IssueDesc where IssueID not in (2)

If I do this, I do not see the record from tableA where the IssueID is NULL, is there any way to pull it up? Or rather why isn't it pulling up the record?

Upvotes: 1

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

NULL "fails" almost all comparisons. What actually happens is that the result is NULL, and the rows get filtered out.

In SQL Server, you need to test for this explicitly:

where IssueID not in (2) OR IssueID is null

Remember that the semantics of NULL generally have it meaning "unknown" value rather than "missing" value. When you have NULL not in (2), the semantics are " not in (2)". The result is "unknown", because the value could be 2.

Upvotes: 4

Related Questions