Typical problems

Three-valued logic and WHERE clause

Let’s take a look at another example, which I borrowed from Joe Selko [7]. Its idea consists of using NOT IN predicate (<list of values including NULL>).

And again, to make it possible for you to check the justice of the discussion on the site, let’s artificially add NULL values to the query result:

Console

Execute

SELECT name, launched, 
 CASE 
 WHEN launched < 1915 
 THEN NULL 
 ELSE launched 
 END year 
FROM Ships 
WHERE launched <= 1915;

SELECT name, launched, CASE WHEN launched < 1915 THEN NULL ELSE launched END year FROM Ships WHERE launched <= 1915;

    Scheme

I specially chose year 1915 to make the resulting set smaller. Here it is:

And now we write a query which must return all vessels, the launch year is not in the values set of the year column:

Console

Execute

SELECT * 
FROM Ships
WHERE launched <= 1916 AND
 launched NOT IN(SELECT year 
 FROM (SELECT name, launched, 
 CASE WHEN launched < 1915 
 THEN NULL 
 ELSE launched 
 END year 
 FROM Ships 
 WHERE launched <= 1915
 ) x
 );

SELECT * FROM Ships WHERE launched <= 1916 AND launched NOT IN(SELECT year FROM (SELECT name, launched, CASE WHEN launched < 1915 THEN NULL ELSE launched END year FROM Ships WHERE launched <= 1915 ) x );

    Scheme

The query

Console

Execute

SELECT * 
FROM Ships
WHERE launched <= 1915;

SELECT * FROM Ships WHERE launched <= 1915;

    Scheme

It seems as we should’ve received Hiei and Kongo ships, as their launch year is known and it’s not 1915. But again we get an empty resulting set.

Let’s evaluate the predicate value for the first of the ships – Hiei (it will be the same for the other vessel). So,

1914 NOT IN (1915, NULL)

I omitted another NULL value for short. The last predicate can be replaced by the following:

1914 <> ALL (1915, NULL)

and that is equivalent to

1914 <> 1915
AND
1914 <> NULL

The last expression is always UNKNOWN, so we can rewrite the predicate:

1914 <> 1915
AND 
UNKNOWN

Therefore, the whole expression will be UNKNOWN as well, because the first comparison gives TRUE. If the first comparison was FALSE (for year 1915), the result would be FALSE.

So, we can make a conclusion that, when having NULL values in data set, NOT IN predicate in WHERE clause will always give an empty resulting set.

Finally, it should be said that if you do a vertical fragmentation of a table using some threshold column value, which allows NULL values, the joining of fragments like

Console

Execute

SELECT * 
FROM Ships 
WHERE launched <= 1915
UNION
SELECT * 
FROM Ships 
WHERE launched > 1915;

SELECT * FROM Ships WHERE launched <= 1915 UNION SELECT * FROM Ships WHERE launched > 1915;

    Scheme

Console

Execute

SELECT * 
FROM Ships 
WHERE launched IS NULL;

SELECT * FROM Ships WHERE launched IS NULL;

    Scheme