Three-valued logic and WHERE clause

Let’s examine the following example.

If we write

Solution 8.5.1

SELECT *
FROM Ships
WHERE launched = NULL;

SELECT *
FROM (SELECT name, launched,
CASE
WHEN launched < 1940
THEN NULL
ELSE launched
END year
FROM Ships
) x
WHERE year = NULL;

Here we added the ‘year’ field that contains NULL in case the vessel was launched earlier than 1940 to the subquery.

So why didn’t we get anything? At this point one should remember that in SQL, as well as in the relational theory, three-valued logic is used. That means the true value of a comparison operation can be not only TRUE or FALSE, but also UNKNOWN. It’s caused by NULL values existence, and the comparison with it brings this case about. It’s intuitively understandable, if you keep in mind Null value is used to replace undefined data. If we ask: “Is the year of Bismarck’s launch 1939?” the answer will be: “I don’t know”, as there is no information on the launch year of this vessel in the database. And this “don’t know” turns into UNKNOWN.

And what happens if we use comparison with NULL value explicitly or implicitly (with the NULL-value in a column we compare with) in WHERE clause? The entry falls into the resulting set if the predicate gives TRUE. And that’s all – in case of FALSE or UNKNOWN the entry is not included. That’s why we got nothing in the example above, as we have UNKNOWN for all rows.

So, how can we get a list of ships with undefined launch year? For this purpose there’s a special IS NULL predicate in SQL Standard (and its opposite IS NOT NULL). The true value of this predicate can’t be UNKNOWN, that means, the year is whether known (FALSE) or not (TRUE). Then we can write to solve our problem:

Solution 8.5.2

SELECT *
FROM Ships
WHERE launched IS NULL;

This is the standard, but what about realizations? All said above is correct about SQL Server. But it’s not the only way. Probably to make SQL programming more customary for those who use traditional programming languages it’s possible to turn off the standard NULL-value interpretation (it’s set on by default) by changing the ANSI_NULLS parameter properly:

SET ANSI_NULLS OFF|ON

Type the following code in Management Studio (or in Query Analyzer for SQL Server 2000)  and everything will become clear:

SET ANSI_NULLS OFF
SELECT *
FROM (SELECT name, launched,
CASE
WHEN launched < 1940
THEN NULL
ELSE launched
END year
FROM Ships
) x
WHERE year = NULL;