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.)