Joe Celko s SQL for Smarties - Advanced SQL Programming P23. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended audience. | 192 CHAPTER 6 NULLS MISSING DATA IN SQL 4 5 Now insert a null and reexecute the same query INSERT INTO Tablel coll VALUES NULL SELECT coll FROM Table2 WHERE coll NOT IN SELECT coll FROM Tablel The result will be empty. This is counterintuitive but correct. The not in predicate is defined as SELECT coll FROM Table2 WHERE NOT coll IN SELECT coll FROM Tablel The in predicate is defined as SELECT coll FROM Table2 WHERE NOT coll ANY SELECT coll FROM Tablel This becomes SELECT coll FROM Table2 WHERE NOT coll l OR coll 2 OR coll 3 OR coll 4 OR coll 5 OR coll NULL The last expression is always unknown so applying DeMorgan s laws the query is really SELECT coll FROM Table2 WHERE coll l AND coll 2 AND coll 3 Functions and NULLs 193 AND coll 4 AND coll 5 AND UNKNOWN Look at the truth tables and you will see this always reduces to unknown and an unknown is always rejected in a search condition in a where clause. Standard SQL Solutions SQL-92 solved some of the 3VL three-valued logic problems by adding a new predicate of the form search condition IS NOT TRUE FALSE UNKNOWN This predicate will let you map any combination of three-valued logic to two values. For example age 18 OR gender Female IS NOT FALSE will return TRUE if age IS NULL or gender IS NULL and the remaining condition does not matter. Math and NULLs nulls propagate when they appear in arithmetic expressions - and return NULL results. See Chapter 3 on numeric data types for more details. Functions and NULLs Most vendors propagate nulls in the functions they offer as extensions of the standard ones required in SQL. For example the cosine of a null will be null. There are two functions that convert nulls into values 1. NULLIF V1 V2 returns a NULL when the first parameter equals the second parameter. The function is equivalent to the following case specification CASE WHEN V1 V2 THEN NULL ELSE V1 END 194 CHAPTER 6 NULLS MISSING DATA IN SQL 2. COALESCE V1 V2 V3 . Vn processes the list from left to right
