19:30

# Three-valued logic and WHERE clause

Let’s 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

Console
Execute
`SELECT * FROM Ships WHERE launched = NULL;`
then, however strange it may seem, we won’t get any entries even if such vessels exist there. As there are no ships with a known launch year in the available database, let’s create them to be able to check the truth of this statement:

Console
Execute
`SELECT * FROM (SELECT name, launched,  CASE  WHEN launched < 1940  THEN NULL  ELSE launched  END year  FROM Ships ) xWHERE 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(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 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

Console
Execute
`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  A database management system (DBMS) by Microsoft Corporation.SQL Server 2000)  and everything will become clear:

Console
Execute
`SET ANSI_NULLS OFFSELECT * FROM (SELECT name, launched,  CASE  WHEN launched < 1940  THEN NULL  ELSE launched  END year  FROM Ships ) xWHERE year = NULL;`