How different clauses treat Null/Unknow in SQL Server 2012?

Question

(relatively new to SQL and SQL Server) I am reading SQL Server 2012 T-SQL Fundamentals by Itzik Ben-Gan. Autor states that:

SQL has different treatments for UNKNOWN in different language elements

So, I went through SQL Server documentation, and in particular WHERE clause, but could not find anything specific on how NULL/UNKNOWN is treated.

Could someone point me to correct document or point me to what I am reading wrong in the documentation ? (I highly suspect that answer to my question is buried deep somewhere in the SQL Standard, but it might be way too complex for me, at least at this point.)

Answer 1

There are subtle differences on how UNKNOWN and NULL are treated in the various clauses.

Three clauses where a boolean expression can appear are the WHERE, HAVING and FROM (in the ON sub-clause). In all three, the UNKNOWN is treated the same way.

• WHERE <boolean expression>:

  a row "passes" the <boolean expression> and is returned if the expression evaluates to TRUE. If the expression evaluates to FALSE or UNKNOWN, the row does not pass and is removed from further evaluation of the query.

• HAVING <boolean expression>:

  The rows examined in the HAVING clause are those created by the aggregation of the previous GROUP BY clause. Similar to above a row (created by the aggregation) "passes" the <boolean expression> and is returned if the expression evaluates to TRUE. If the expression evaluates to FALSE or UNKNOWN, the row does not pass and is removed from further evaluation of the query.

• a JOIN b ON <boolean expression>:

  Every row from a is checked against every row from b. If the expression is TRUE, then the check passes and the combination of the two rows is valid for further processing of the query. If FALSE or UNKNOWN, then the combination is rejected.
  There is a slight change when the join is an outer join (LEFT, RIGHT or FULL) but not regarding how UNKNOWN is treated. What happens here is that if a row from the left table (in a LEFT join) matches no row from the right table (i.e. all its checks evaluate to FALSE or UNKNOWN), then this row is still forwarded and the missing values for the b columns are filled with nulls.)

The situation is different in the constraints, there is a more "relaxed" treatment for UNKNOWN. I guess this has to do with the decision of allowing nulls in the first place. A null value will result in an UNKNOWN result in most constraints, so it makes sense to allow those rows to be inserted. Otherwise, the effect would be the same as declaring the columns as NOT NULL.

• CHECK constraint: CHECK <expression>

  a row is allowed to be inserted (or updated) if the expression evaluates to TRUE or to UNKNOWN. It is rejected only if the expression evaluates to FALSE.

• FOREIGN KEY constraint, with nullable columns.

  similar to the check constraints, if a row has a null in one of the columns that participate in a foreign key constraint, the verification of the FOREIGN KEY constraint is skipped and the row is allowed to be inserted (or updated.) One can think of a foreign key constraint in table a:

  FOREIGN KEY (column) REFERENCES b (column)
  

  to be equivalent to:

  CHECK (column IN (SELECT column IN b))
  

  So, if a.column is NULL, the expression is evaluated to UNKNOWN and thus the row is allowed.

• UNIQUE constraints

  Here SQL-Server deviates from the standard which state that if a row with a unique constraint contains NULL, then the unique check is skipped and the row is allowed.
  SQL-Server treats nulls as if they were regular values, when it comes to unique constraints and allows only one NULL value, in such columns.

---

NULL values have also special treatment in some clauses:

• GROUP BY:

  If a grouping column contains null values, all null values are considered equal, and they are put into a single group (the same thing happens in a SELECT DISTINCT query.)

  Aggregate functions (MIN(), MAX(), SUM(), AVG() and others) ignore nulls with the exception of COUNT() which can do a lot of different things:

  • COUNT(*) and COUNT(constant) will count all rows.
  • COUNT(column) and COUNT(expression) will ignore nulls as the other aggregate functions and count only rows where the column or expression is not null.
  • COUNT(DISTINCT column) and COUNT(DISTINCT expression) will also ignore nulls and count only the distinct non-null columns or expressions.

  So the following 4 queries may return 4 different results if there are nulls in the column:

     SELECT COUNT(*) FROM t ;
     SELECT COUNT(c) FROM t ;
     SELECT COUNT(DISTINCT c) FROM t ;
     SELECT COUNT(*) FROM (SELECT DISTINCT c FROM t) AS x ;
  

• ORDER BY:

  • In comparisons between them, NULL values are treated as equal.
  • When comparing them to non-null values, null values are treated as the lowest possible value, lower than any non-null value. As a result, they appear first when the order is ASC and last when the order is DESC.
    (There is a NULLS LAST and NULLS FIRST attribute defined in the latest standard but SQL Server has not implemented it yet.)