Three-valued logic and WHERE clause

Lets examine the following example.

Let it be required to define the ships with the unknown launch year (database Ships).

If we write

Solution 8.5.1

  1. SELECT *
  2. FROM Ships
  3. WHERE launched = NULL;
then, however strange it may seem, we wont get any entries even if such vessels exist there. As there are no ships with a known launch year in the available database, lets create them to be able to check the truth of this statement:

  1. SELECT *
  2. FROM (SELECT name, launched,
  3. CASE
  4. WHEN launched < 1940
  6. ELSE launched
  7. END year
  8. FROM Ships
  9. ) x
  10. 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 didnt 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. Its caused by NULL values existence, and the comparison with it brings this case about. Its intuitively understandable, if you keep in mind Null value is used to replace undefined data. If we ask: Is the year of Bismarcks launch 1939? the answer will be: I dont know, as there is no information on the launch year of this vessel in the database. And this dont 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 thats all in case of FALSE or UNKNOWN the entry is not included. Thats 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 theres a special IS NULL predicate in  SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Standard (and its opposite IS NOT NULL). The true value of this predicate cant 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

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

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


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

  2. SELECT *
  3. FROM (SELECT name, launched,
  4. CASE
  5. WHEN launched < 1940
  7. ELSE launched
  8. END year
  9. FROM Ships
  10. ) x
  11. WHERE year = 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 CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100