Not Equal logic trouble in SQL Server

Question

I can't believe I'm having so much trouble with this.

Using this statement:

USE XXXX

SELECT 
    ID, DESCRIPTION, STATUS
FROM 
    PART
WHERE 
    PART.ID LIKE 'PCH%'
    AND PART.DESCRIPTION NOT LIKE '%OBSOLETE%'
    AND PART.STATUS = 'O'

I get a table with 34 entries, each of them containing O in PART.STATUS.

What I actually want to say is, only show me the values which do NOT have a status of O. I know there are other ways around this. values that are not O should be null, but I'm annoyed that I can't figure out how the 'not equal' statement works. When I switch the last line to:

AND PART.STATUS <> 'O'

OR

AND PART.STATUS != 'O'

I get an empty table returned.

If I use the line

AND PART.STATUS IS NULL

I get the table I'm looking for.

What am I misunderstanding about the use of 'not equal statements'?

You **cannot** use the normal equality (`=`) or inequality (`<>`) operators against `NULL` - using those against a `NULL` doesn't return a proper value - it returns `NULL`. In order to check for `NULL`, you **have** to use `IS NULL` or `IS NOT NULL` - there's no other way — marc_s, Feb 25 '15 at 16:08

Giorgi Nakeuri · Answer 1 · 2015-02-25T16:22:35.557

The problem is 3 valued predicate logic. When at least one side of predicate is NULL the result of predicate is UNKNOWN(no matter you use = or <> or > or <, ...), but WHERE clause only returns rows where predicate evaluates to TRUE. So our job is to make predicate to evaluate to TRUE when PART.STATUS IS NULL. This is done by adding additional check on NULL like:

USE XXXX
SELECT ID, DESCRIPTION,STATUS
FROM PART
WHERE 
PART.ID LIKE 'PCH%'
AND PART.DESCRIPTION NOT LIKE '%OBSOLETE%'
AND (PART.STATUS <> 'O' OR PART.STATUS IS NULL)

Here is a little example. Imagine this is your table and you are issuing your statement WHERE PART.STATUS <> 'O'

PART(STATUS)
'A'
'O'
NULL

It evaluates to:

WHERE 'A' <> 'O' --TRUE
WHERE 'O' <> 'O' --FALSE
WHERE NULL <> 'O'--UNKNOWN

Since WHERE clause returns only rows where result of predicate is TRUE, you will get only 'A' here.

Right, even `null = null` is not `true` :) The Wikipedia page for [SQL Null](http://en.wikipedia.org/wiki/Null_(SQL)) is always helpful for a thorough discussion — Andomar, Feb 25 '15 at 16:17
@Andomar, yes there should be `at least one side of predicate is NULL`. Edited. — Giorgi Nakeuri, Feb 25 '15 at 16:23

score 1 · Answer 2 · answered Feb 25 '15 at 15:57

the issue here is with the nullvalue since every logical comparation against it would return false for example

PART.STATUS = NULL-- Would be false
PART.STATUS <> NULL-- would also return false

so you should do your comparison like

AND (PART.STATUS <> 'O' OR PART.STATUS IS NULL)

score 1 · Answer 3 · edited May 23 '17 at 12:28

1

The part you're missing is that NULL is not a value, but the absence of it. A NULL means that the value in that field is either inexistent or unknown. That's why you cant' directly compare a value to a NULL. As stated in other answers, you have to use PART.STATUS IS NULL. Another option would be to use the ISNULL function, wich will test a value for NULL and, if it is, will return whatever value you specify on the second parameter. E.g.

USE XXXX
SELECT ID, DESCRIPTION,STATUS
FROM PART
WHERE 
PART.ID LIKE 'PCH%'
AND PART.DESCRIPTION NOT LIKE '%OBSOLETE%'
AND ISNULL(PART.STATUS, '') <> 'O'

Check the documentation for NULL in Sql Server and the ISNULL function. Also, this question could be of use.

edited May 23 '17 at 12:28

Community

1
1

answered Feb 25 '15 at 16:06

Josh Part

2,154
12
15

But you can directly compare `null` to a value. The result will be `unknown`. Not sure what you mean by "that field is either inexistent or unknown". The `null` business is weird enough without introducing types of `null` :) – Andomar Feb 25 '15 at 16:20
Well, as long as you can write `[some value] = null`, of course you can *compare* a value to a null... but the result will always be `unknown` as you state. Yeah, `null` is an entire logic on it's own, and I was only trying to rephrase what the documentation says. I think is one of those thinks that everybody can get in a general idea but will never really understand what it does under the hood. – Josh Part Feb 25 '15 at 16:30
When you read the [Wikipedia article](http://en.wikipedia.org/wiki/Null_(SQL)) you'll understand it (or at least, that worked for me.) The two gotchas are the existence of `unknown` on the same level as `true` or `false`. And that `null` is a value and `unknown` the result of an expression. `where null = 1` and `where unknown` are conceptually valid, but not `where null` or `where unknown = 1` do not. – Andomar Feb 25 '15 at 17:01

score 0 · Answer 4 · answered Feb 25 '15 at 15:57

0

USE XXXX
SELECT ID, DESCRIPTION,STATUS
FROM PART
WHERE 
PART.ID LIKE 'PCH%'
AND PART.DESCRIPTION NOT LIKE '%OBSOLETE%'
OR PART.STATUS != 'O'

answered Feb 25 '15 at 15:57

Khurram Ali

1,659
4
20
37

1

Explain how this differs from the code posted in the question. I had to read it four times before I saw it! – Glenn Randers-Pehrson Feb 25 '15 at 16:20
@GlennRanders-Pehrson: in the question he used `AND PART.STATUS != 'O'` while in my answer i use `or` – Khurram Ali Feb 26 '15 at 03:07

score 0 · Answer 5 · answered Feb 25 '15 at 15:59

0

Maybe your misunderstanding is probably about the NULL concept. NULL is not an empty string neither different from 'O', it's just NULL.

That's why you have to use

AND PART.STATUS IS NULL

or

AND isnull(PART.STATUS, '') <> 'O'

answered Feb 25 '15 at 15:59

Rubik

1,431
1
18
24

score 0 · Answer 6 · answered Feb 25 '15 at 16:04

It is important to remember how NULL is treated in a database. It isn't a value at all!

No operator (>,<,=.. etc) less (is) used will ever return rows with NULL.

writing in SQL "where column = NULL" is like saying "give me all rows where the value isn't a value

score -1 · Answer 7 · answered Feb 25 '15 at 16:02

-1

Null values can't be compared with equals(=) or not equals operators. Try the below:

USE XXXX
SELECT ID, DESCRIPTION,STATUS FROM PART WHERE 
PART.ID LIKE 'PCH%'
AND PART.DESCRIPTION NOT LIKE '%OBSOLETE%'
AND ISNULL(PART.STATUS, '') <> 'O'

answered Feb 25 '15 at 16:02

SouravA

5,147
2
24
49

Not Equal logic trouble in SQL Server

7 Answers7