Prepare for the second stage of testing

Typical problems

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;

SELECT * FROM Ships WHERE launched = NULL;

    Scheme

Console

Execute

SELECT * 
FROM (SELECT name, launched, 
 CASE 
 WHEN launched < 1940 
 THEN NULL 
 ELSE launched 
 END year 
 FROM Ships
 ) x
WHERE year = NULL;

SELECT * FROM (SELECT name, launched, CASE WHEN launched < 1940 THEN NULL ELSE launched END year FROM Ships ) x WHERE year = NULL;

    Scheme

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;

SELECT * FROM Ships WHERE launched IS NULL;

    Scheme

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 OFF
SELECT * 
FROM (SELECT name, launched, 
 CASE 
 WHEN launched < 1940 
 THEN NULL 
 ELSE launched 
 END year 
 FROM Ships
 ) x
WHERE year = NULL;

SET ANSI_NULLS OFF SELECT * FROM (SELECT name, launched, CASE WHEN launched < 1940 THEN NULL ELSE launched END year FROM Ships ) x WHERE year = NULL;

    Scheme

Content:

(8.5.1.) NOT IN predicate