NULL values in SQL server query

Question

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.

Solution 1

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.

Solution 2

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.

Solution 3

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.

Solution 4

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.

Solution 5

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.

View more solutions

Comments

[Andomar]

A comparison to null returns unknown, not false

[TimothyAWiseman]

I agree that you should explicitly use is not null for clarity reasons for the next human, however that is normal behavior for the server given default configuration. I also agree with the habit about specifying a column list under normal circumstances, but there are rare times when * is truly appropriate in ways that a complete column list isn't.

[TimothyAWiseman]

@Andomar From a three value logic perspective, that makes sense. For the internals of SQL Server, the documentation indicates it really is false. msdn.microsoft.com/en-us/library/aa259229(v=sql.80).aspx see second paragraph under remarks.

[Martin Smith]

@TimothyAWiseman - The documentation is wrong then. Probably no one has bothered correcting it as it is for SQL Server 2000. Updated version of that article here. If it evaluated to false then NOT (status <> 'N/A') would be true. It isn't. Also a check constraint with NULL <> 'N/A' would not allow any rows to be inserted. It does.