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
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:
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:
Type the following code in Management Studio (or in Query Analyzer for Cèñòåìà óïðàâëåíèÿ ðåëÿöèîííûìè áàçàìè äàííûõ (ÑÓÁÄ), ðàçðàáîòàííàÿ êîðïîðàöèåé Microsoft.SQL Server 2000) and everything will become clear: