Sql server® 2012 t-sql fundamentals

 

CHAPTER 2

 

Single-Table Queries

 

 59

5           Sweden          NULL            Luleå 

6           Germany         NULL            Mannheim 

7           France          NULL            Strasbourg 

8           Spain           NULL            Madrid 

9           France          NULL            Marseille 

10          Canada          BC              Tsawassen 

... 

 

(88 row(s) affected)

SQL also treats NULL marks inconsistently in different language elements for comparison and sort-

ing purposes. Some elements treat two NULL marks as equal to each other and others treat them as 

different.

For example, for grouping and sorting purposes, two NULL marks are considered equal. That is, 

the GROUP BY clause arranges all NULL marks in one group just like present values, and the ORDER 

BY clause sorts all NULL marks together. Standard SQL leaves it to the product implementation as 

to whether NULL marks sort before present values or after. T-SQL sorts NULL marks before present 

values.

As mentioned earlier, query filters “accept TRUE.” An expression comparing two NULL marks yields 

UNKNOWN; therefore, such a row is filtered out.

For the purposes of enforcing a UNIQUE constraint, standard SQL treats NULL marks as different 

from each other (allowing multiple NULL marks). Conversely, in T-SQL, a UNIQUE constraint considers 

two NULL marks as equal (allowing only one NULL if the constraint is defined on a single column).

Keeping in mind the inconsistent treatment SQL has for UNKNOWN and NULL marks and the 

potential for logical errors, you should explicitly think of NULL marks and three-valued logic in every 

query that you write. If the default treatment is not what you want, you must intervene explicitly; 

otherwise, just ensure that the default behavior is in fact what you want.