Null (SQL) - When Two Nulls Are Equal: Grouping, Sorting, and Some Set Operations

When Two Nulls Are Equal: Grouping, Sorting, and Some Set Operations

Because SQL:2003 defines all Null markers as being unequal to one another, a special definition was required in order to group Nulls together when performing certain operations. SQL defines "any two values that are equal to one another, or any two Nulls", as "not distinct". This definition of not distinct allows SQL to group and sort Nulls when the GROUP BY clause (and other keywords that perform grouping) are used.

Other SQL operations, clauses, and keywords use "not distinct" in their treatment of Nulls. These include the following:

• PARTITION BY clause of ranking and windowing functions like ROW_NUMBER
• UNION, INTERSECT, and EXCEPT operator, which treat NULLs as the same for row comparison/elimination purposes
• DISTINCT keyword used in SELECT queries

The principle that Nulls aren't equal to each other (but rather that the result is Unknown) is effectively violated in the SQL specification for the UNION operator, which does identify nulls with each other. Consequently, some set operations in SQL, like union or difference, may produce results not representing sure information, unlike operations involving explicit comparisons with NULL (e.g. those in a WHERE clause discussed above). In Codd's 1979 proposal (which was basically adopted by SQL92) this semantic inconsistency is rationalized by arguing that removal of duplicates in set operations happens "at a lower level of detail than equality testing in the evaluation of retrieval operations."

The SQL standard does not explicitly define a default sort order for Nulls. Instead, on conforming systems, Nulls can be sorted before or after all data values by using the NULLS FIRST or NULLS LAST clauses of the ORDER BY list, respectively. Not all DBMS vendors implement this functionality, however. Vendors who do not implement this functionality may specify different treatments for Null sorting in the DBMS.