IS NOT NULL and OR

Question

I have a query which includes a IS NOT NULL condition. I also needed to set an OR condition on the same column (C1).

I wrote the query as I would normally when including an OR condition. I did not get the right results. Here is the query.

SELECT COUNT(*) FROM    T1 WHERE  (C1 IS NOT NULL AND C2 = 'UK')  OR (C1 <> 'Pass'AND C2 = 'UK')

I removed the OR condition in parentheses and included the extra condition as an AND.. it worked. Do things work differently when using IS NOT NULL

SELECT COUNT(*)

FROM    T1 WHERE  (C1 IS NOT NULL AND C1 <> 'Pass' AND C2 = 'UK') 

---

Mr Shaw

Answer 1

Hi, the only problem with your first query, is that te block ((C1 <> 'Pass'AND C2 = 'UK') would always accept any NULL values in the C1 column and would evaluate it as TRUE. As it is in a OR condition, if this block is TRUE, the result would be wrong and the other block would be disconsidered.

Could you understand?

Please forgive any English mistakes.

Hope it helped.

Answer 2

Hello,

Your both statements / logic to filter are different. The optimization of the boolean logic of the first query would be

SELECT COUNT(*) 
FROM    T1 
WHERE  C2 = 'UK' 
       AND (C1 IS NOT NULL OR C1 <> 'Pass')

instead. OR, not AND!

---

Olaf Helper

Blog Xing

Answer 3

HI

Condition below

(C1 IS NOT NULL AND C2 = 'UK')  OR (C1 <> 'Pass'AND C2 = 'UK')

is not same as

 (C1 IS NOT NULL AND C1 <> 'Pass' AND C2 = 'UK')  

two condition has different meaning so that you find difference .

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 4

SELECT COUNT(*) FROM    T1 WHERE  (C1 IS NOT NULL AND C2 = 'UK')  OR (C1 <> 'Pass'AND C2 = 'UK')

SELECT COUNT(*) FROM    T1 WHERE  (C1 IS NOT NULL AND C1 <> 'Pass' AND C2 = 'UK')

As others have noted, these are not the same queries.  To see why suppose C1 = 'Pass' and C2 = 'UK'. 

Then the first query is WHERE (TRUE AND TRUE) OR (FALSE AND TRUE) which is the same as TRUE OR FALSE so the final result is TRUE.

But the second query is (TRUE AND FALSE AND TRUE) so the final result is FALSE.

So the first query will include rows where C1 = 'Pass' and C2 = 'UK', but the second one will not.

Tom

Answer 5

It is not clear from your description what result you are after, or rather, why you are after the result that your final query returns.

But I will explain your first query first, because some people are posting incorrect information in this thread.

For starters, the predicate "C1 <> 'Pass'" in a WHERE clause will retain the row for any value of C1 that is not 'Pass' and that is not NULL. If C1 is NULL, then the predicate evaluates to UNKNOWN, and the row will be discarded from the result set.

Your predicate "C1 IS NOT NULL" will retain all rows where C1 is not NULL.

So basically, your expression "(C1 <> 'Pass' AND C2='UK')" is a subset of "(C1 IS NOT NULL AND C2 = 'UK')". Because of your OR, the optimizer can reduce your first query to the following equivalent:

SELECT COUNT(*) FROM T1 WHERE C1 IS NOT NULL AND C2 = 'UK'

Now your last query has logic flaws too. Again, "C1 <> 'Pass'" is a subset of "C1 IS NOT NULL". Because of the AND, the equivalent of your last query is:

SELECT COUNT(*) FROM T1 WHERE C1 <> 'Pass' AND C2 = 'UK'

So what are your worries about NULLs in this query?

---

Gert-Jan

Answer 6

I don't want the results for C1 to include any NULL values or 'Pass' values.

---

Mr Shaw

Answer 7

You can write the query in this way also:

SELECT * FROM T1

WHERE C2 = 'UK'

AND

(C1 IS NOT NULL OR C1 <> 'PASS')

HOPE THIS WILL WORK FOR YOU :)

---

Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

Answer 8

I don't want the results for C1 to include any NULL values or 'Pass' values.

---

Mr Shaw

The predicate "C1 <> 'Pass'" will do that. You can leave out the "C1 IS NOT NULL" part...

---

Gert-Jan

Answer 9

You don't need null statements :)

SELECT COUNT(*)
FROM   T1 WHERE
C1 <> 'Pass' AND C2 = 'UK';

^^this will return/count only non-null values that are not equal to pass (not case sensitive).

When you say c1<>'pass' nulls are excluded because nulls are unknowns and sql will not return the unknowns.

if you want to forget about the 'Pass' qualificaiton It can also be done this way because TSQL will not count the nulls either.  Count(*) counts rows and they wont be null:

SELECT count(c1)
FROM T1
WHERE c2='UK';