Issue with null value using select statement

Question

I am trying to use this select statement but my issue is for this ID that i am trying to use in my select statement has null value. Even though ID 542 has null value but i know for fact in the future is going to have a 'COMPLETE' value in it. The 3 possible values for the FLAG field are COMPLETE, NOT COMPLETE AND NULL. What i want to achieve with this select statement is to see all records where FLAG is not 'COMPLETE'. If i run my query now, it will not return anything but if i remove FLAG <>'COMPLETE' then it will return the record ID 542 but the flag value is null. Here is my code

SELECT ID, DT, STAT FROM myTable WHERE ID = 542 and FLAG <> 'COMPLETE'

Milen · Accepted Answer · 2014-03-11 17:58:17Z

2

Convert the NULL to text:

SELECT ID, DT, STAT 
FROM myTable 
WHERE ID = 542 and ISNULL(FLAG,'NULL') <> 'COMPLETE'

edited Mar 11, 2014 at 17:58

Milen

8,8877 gold badges47 silver badges59 bronze badges

answered Mar 11, 2014 at 17:47

Rob

3,3892 gold badges27 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Milen · Accepted Answer · 2014-03-11 17:55:49Z

1

SELECT ID, DT, STAT 
FROM myTable 
WHERE ID = 542 and ISNULL(FLAG, 'NOT COMPLETE') <> 'COMPLETE'

Since the FLAG is null it cannot be compared against 'COMPLETE' and you're missing the entry...

or:

SELECT ID, DT, STAT 
FROM myTable 
WHERE ID = 542 AND (FLAG <> 'COMPLETE' OR FLAG IS NULL)

edited Mar 11, 2014 at 17:55

answered Mar 11, 2014 at 17:50

Milen

8,8877 gold badges47 silver badges59 bronze badges

Comments

Andomar · Accepted Answer · 2014-03-11 17:56:27Z

Welcome to SQL's three valued logic! The well-known brothers true and false have a secret stepsister called unknown. And the result of a comparison with null is always unknown.

A quick fix is to add an explicit is null check:

where Flag is null or Flag <> 'COMPLETE'

Note that not unknown is still unknown, so this won't work:

where not Flag = 'COMPLETE' -- Won't work

In fact, not even null is equal to null. But null isn't not equal to null either!

For more fun, have a look at Wikipedia. It has a whole page dedicated to three valued logic in SQL.

Collectives™ on Stack Overflow

Issue with null value using select statement

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related