CODEWITHSUNDEEP
sqlsql-servert-sql
Eliza Smith
Eliza Smith

Reputation: 33

NULL values in SQL server query

I've a Status column in a table which has 3 values - 'N/A' , 'Single' ,'Multiple'. Some rows have a NULL value for the Status column.

I need to pull up all the rows in which Status is not null and is not 'N/A'. Basically, I need all the rows whose status is "Single" or "Multiple".

I've been just reading up about NULL actually being equivalent to 'UNKNOWN'.

If I say 

SELECT *
FROM t_userstatus
WHERE status <> 'N/A'

I get the results (All rows containing "Single" or "Multiple" only).

What I would like to know is that , does the above WHERE clause always exclude the rows having NULL values?Is that the expected behaviour?

What causes this to exclude null rows even though I've not explicitly specified it?

In my query,do I have to explicitly say status IS NOT NULL ?

I am relatively new to programming, any help is appreciated.

Upvotes: 3

Views: 14676

Answers (5)

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

A bit non-obviously, SQL has three valued logic (true/false/unknown). Any direct comparison with NULL will result in the value unknown which in the WHERE clause is considered non-true. Your condition;

WHERE status <> 'N/A'

will in other words never match a null since NULL is never "not equal" to anything.

The only ways to match a NULL using a comparison is using the special null operations IS NULL or IS NOT NULL.

As a side note, life is naturally never quite that simple.

Upvotes: 2

TimothyAWiseman
TimothyAWiseman

Reputation: 14883

As others have said, null is generally not comparable. So, a status != 'N/A' comes back as false when status is null.

But what others haven't mentioned yet is that that is controlled through the Ansi_nulls setting. By default it is on and nulls are not comparable (as you state in the question, the principal behind this is that they are unknown so it is false that they are not necessarily N/A). But you can use 

SET ANSI_NULLS OFF

To change this behavior in which case a null will be equal to a null and not equal to anything else. There is more details on MSDN here.

Upvotes: 2

Kermit
Kermit

Reputation: 34062

It is not normal behavior as N/A (Single & Multiple as well) is a string that is not related to NULL. Even though the NULL is evaluated to unknown and may not return, you should explicitly use IS NOT NULL.

SELECT [column_list] FROM t_userstatus
WHERE status IS NOT NULL AND status <> 'N/A'

I would also recommend you get in the habit of specifying a column list.

Upvotes: 6

Andomar
Andomar

Reputation: 238296

SQL uses three-valued logic: true, false, and unknown. Any comparison to null results in unknown.

So null <> 'N/A' evaluates to unknown. Since unknown is not true, that means the row gets excluded.

Upvotes: 5

BellevueBob
BellevueBob

Reputation: 9618

Yes, that is the normal behavior. A NULL value has no value, so it does not satisfy a WHERE condition. If you want to also include null values, you need to specify IS NOT NULL as well.

Upvotes: 2

Related Questions