Sql server® 2012 t-sql fundamentals

NULL Marks

As explained in Chapter 1, “Background to T-SQL Querying and Programming,“ SQL supports the 

NULL mark to represent missing values and uses three-valued logic, meaning that predicates can 

evaluate to TRUE, FALSE, or UNKNOWN. T-SQL follows the standard in this respect. Treatment of 

NULL marks and UNKNOWN in SQL can be very confusing because intuitively people are more 

accustomed to thinking in terms of two-valued logic (TRUE and FALSE). To add to the confusion, dif-

ferent language elements in SQL treat NULL marks and UNKNOWN differently.

Let’s start with three-valued predicate logic. A logical expression involving only existing or present 

values evaluates to either TRUE or FALSE, but when the logical expression involves a missing value, 

it evaluates to UNKNOWN. For example, consider the predicate salary > 0. When salary is equal to 

1,000, the expression evaluates to TRUE. When salary is equal to –1,000, the expression evaluates to 

FALSE. When salary is NULL, the expression evaluates to UNKNOWN. 

www.it-ebooks.info

---

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

---