NOT IN predicate

Lets 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, lets artificially add NULL values to the query result:

  1. SELECT name, launched,
  2. CASE
  3. WHEN launched < 1915
  5. ELSE launched
  6. END year
  7. FROM Ships
  8. WHERE launched <= 1915;

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

name launched year
Hiei 1914 NULL
Kirishima 1915 1915
Kongo 1913 NULL

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

  1. SELECT *
  2. FROM Ships
  3. WHERE launched <= 1916 AND
  4. launched NOT IN(SELECT year
  5. FROM (SELECT name, launched,
  6. CASE WHEN launched < 1915
  8. ELSE launched
  9. END year
  10. FROM Ships
  11. WHERE launched <= 1915
  12. ) x
  13. );

The query

  1. SELECT *
  2. FROM Ships
  3. WHERE launched <= 1915;
gives us the following ships selection:

name class launched
Hiei Kongo 1914
Kirishima Kongo 1915
Kongo Kongo 1913

It seems as we shouldve received Hiei and Kongo ships, as their launch year is known and its not 1915. But again we get an empty resulting set.

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

  1. 1914 NOT IN (1915, NULL)

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

  1. 1914 <> ALL (1915, NULL)

and that is equivalent to

  1. 1914 <> 1915
  2. AND
  3. 1914 <> NULL

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

  1. 1914 <> 1915
  2. AND

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

  1. SELECT *
  2. FROM Ships
  3. WHERE launched <= 1915
  4. UNION
  5. SELECT *
  6. FROM Ships
  7. WHERE launched > 1915;
does not grant the full composition of the initial table. Youll need another fragment that contains NU L-values in the launched column.

  1. SELECT *
  2. FROM Ships
  3. WHERE launched IS NULL;

Bookmark and Share
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.