Let’s start with three-valued predicate logic. A logical expression involving only existing or present
56
Microsoft SQL Server 2012 T-SQL Fundamentals
SQL treats TRUE and FALSE in an intuitive and probably expected manner. For example, if the
predicate salary > 0 appears in a query filter (such as in a WHERE or HAVING clause), rows or groups
for which the expression evaluates to TRUE are returned, whereas those for which the expression
evaluates to FALSE are filtered out. Similarly, if the predicate salary > 0 appears in a CHECK constraint
in a table, INSERT or UPDATE statements for which the expression evaluates to TRUE for all rows are
accepted, whereas those for which the expression evaluates to FALSE for any row are rejected.
SQL has different treatments for UNKNOWN in different language elements (and for some peo-
ple, not necessarily the expected treatments). The correct definition of the treatment SQL has for
query filters is “accept TRUE,” meaning that both FALSE and UNKNOWN are filtered out. Conversely,
the definition of the treatment SQL has for CHECK constraints is “reject FALSE,” meaning that both
TRUE and
UNKNOWN are accepted. If SQL used two-valued predicate logic, there wouldn’t be a
difference between the definitions “accept TRUE” and “reject FALSE.” But with three-valued predi-
cate logic, “accept TRUE” rejects UNKNOWN (it accepts only TRUE, hence it rejects both FALSE and
UNKNOWN), whereas “reject FALSE” accepts it (it rejects only FALSE, hence it accepts both TRUE
and UNKNOWN). With the predicate salary > 0 from the previous example, a NULL salary would
cause the expression to evaluate to UNKNOWN. If this predicate appears in a query’s WHERE clause,
a row with a NULL salary will be filtered out. If this predicate appears in a CHECK constraint in a
table, a row with a NULL salary will be accepted.
One of the tricky aspects of UNKNOWN is that when you negate it, you still get UNKNOWN. For
example, given the predicate NOT (salary > 0), when salary is NULL, salary > 0 evaluates to UNKNOWN,
and NOT UNKNOWN remains UNKNOWN.
What some people find surprising is that an expression comparing two NULL marks (NULL = NULL)
evaluates to UNKNOWN. The reasoning for this from SQL’s perspective is that a NULL represents a
missing or unknown value, and you can’t really tell whether one unknown value is equal to another.
Therefore, SQL provides you with the predicates IS NULL and IS NOT NULL, which you should use
instead of = NULL and <> NULL.
To make things a bit more tangible, I’ll demonstrate the aforementioned aspects of the three-
valued predicate logic. The Sales.Customers table has three attributes called country, region, and city,
where the customer’s location information is stored. All locations have existing countries and cities.
Some have existing regions (such as country: USA, region: WA, city: Seattle), yet for some the region
element is missing and inapplicable (such as country: UK, region: NULL, city: London). Consider the
following query, which attempts to return all customers where the region is equal to WA.
SELECT custid, country, region, city
FROM Sales.Customers
WHERE region = N'WA';
This query generates the following output.
custid country region city
----------- --------------- --------------- ---------------
43 USA WA Walla Walla
82 USA WA Kirkland
89 USA WA Seattle
www.it-ebooks.info