CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3220935
user3220935

Reputation: 1

SQL: Check for 'value' is not null returns TRUE when 'value' is null

I am using SQL Server 2008, and when I perform a left join to a table where the outer table does not have any records then I am seeing weird behavior from my where clause. If I do a check for a value from my outer table being 'not null' it sometimes returns true.

select *
from foo f left join bar b on b.id=f.id
where f.id=@id and (f.status = 1 or b.Price is not null)

When my f.status = 0 and b.Price does not exist (or, appears as null in the select) this query selects records where f.status = 0 and b.Price is null, even though (FALSE OR FALSE) should be FALSE.

If I just perform this query, it works as expected and anything without a record in 'bar' does not get selected.

select *
from foo f left join bar b on b.id=f.id
where f.id=@id and b.Price is not null

Having b.Price is not null as part of an or operation seems to be causing issue for some reason. What could be wrong with this query? I run the same query with similar data on a SQL Server 2012 machine and do not see this issue, could it be related the the version of SQL Server I am using?

Upvotes: 0

Views: 3744

Answers (3)

Jeff Moretti
Jeff Moretti

Reputation: 683

Would a CASE statement work?

IE

SELECT

(etc etc code)

CASE WHEN b.Price is not null THEN 1 ELSE 0 END AS [MyBooleanCheck]

FROM (etc etc code)

Upvotes: 1

Andrei Hirsu
Andrei Hirsu

Reputation: 696

You could try an OUTER APPLY like this:

SELECT *
FROM foo f
OUTER APPLY (
SELECT *
FROM bar b
WHERE f.id = b.id
    AND (
        f.STATUS = 1
        OR b.Price IS NOT NULL
        ) b
)
WHERE f.id = @id

And I also suggest using the columns instead of *, bad practice. The Outer Apply is sort of like a left join and in this case it will filter all the data from the bar table and bring you back only the data you need.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

These two formulations are not the same, as you have discovered.

In the first query, price can be NULL for two reasons:

  • There is no match from the left join.
  • There is a match and b.Price is null

I highly recommend the second approach, putting the condition in the on clause. However, if you do use the first one, make the comparison to a column used in the join:

where f.id = @id and (f.status = 1 or b.id is not null)

Upvotes: 1

Related Questions