Joe Celko s SQL for Smarties - Advanced SQL Programming P28. 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. | 242 CHAPTER 10 VALUED PREDICATES non-NULL values is defined by Table where Degree means the number of columns in the row expression. Table Cases Where a Row Is a Mix of NULL and non-NULL Values Expression R IS NULL R IS NOT NULL NOT R IS NULL NOT R IS NOT NULL - Degree 1 NULL TRUE FALSE FALSE TRUE No NULL FALSE TRUE TRUE FALSE - Degree 1 All NULLs TRUE FALSE FALSE TRUE Some NULLs FALSE FALSE TRUE TRUE No NULLs FALSE TRUE TRUE FALSE Note that R is not null has the same result as not r is null if and only if R is of degree 1. This is a break in the usual pattern of predicates with a not option in them. Here are some examples 1 2 3 IS NULL FALSE 1 NULL 3 IS NULL FALSE 1 NULL 3 IS NOT NULL FALSE NULL NULL NULL IS NULL TRUE NULL NULL NULL IS NOT NULL FALSE NOT 1 2 3 IS NULL TRUE NOT 1 NULL 3 IS NULL TRUE NOT 1 NULL 3 IS NOT NULL TRUE NOT NULL NULL NULL IS NULL FALSE NOT NULL NULL NULL IS NOT NULL TRUE Sources of NULLs It is important to remember where NULLs can occur. They are more than just a possible value in a column. Aggregate functions on empty sets outer joins arithmetic expressions with nulls and OLAP operators all return nulls. These constructs often show up as columns in views. IS NOT TRUE FALSE UNKNOWN Predicate This predicate tests a condition that has the truth-value true false or UNKNOWN and returns TRUE or FALSE. The syntax is O IS NOT TRUE FALSE UNKNOWN Predicate 243 Boolean test Boolean primary IS NOT truth value truth value TRUE FALSE UNKNOWN Boolean primary predicate left paren search condition right paren As you would expect the expression IS not logical value is the same as NOT IS logical value so the predicate can be defined as shown in Table . Table Defining the Predicate True False or Unknown IS condition TRUE FALSE UNKNOWN TRUE TRUE FALSE FALSE FALSE FALSE TRUE FALSE UNKNOWN FALSE FALSE TRUE If you are familiar with some of Chris Date s writings his MAYBE x predicate is not the same as the ANSI x is not false .
