Null (SQL): Difference between revisions

Revision as of 15:35, 7 November 2024

Revision as of 15:41, 7 November 2024

Line 617:

Other SQL operations, clauses, and keywords using the "not distinct" definition in their treatment of Nulls include:

* The <code>PARTITION BY</code> clause of the ranking and windowing functions likesuch as <code>ROW_NUMBER</code>

* The <code>UNION</code>, <code>INTERSECT</code>, and <code>EXCEPT</code> operatoroperators, which treat NULLs as the same for row comparison/elimination purposes

* The <code>DISTINCT</code> keyword used in <code>SELECT</code> queries

The principle that Nulls aren'tare not equal to each other (but rather that the result is Unknown) is effectively violated in the SQL specification for the <code>UNION</code> operator, which does identify nulls with each other.<ref name="Meyden"/> 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 <code>WHERE</code> 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."<ref name="Klein"/>

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 <code>NULLS FIRST</code> or <code>NULLS LAST</code> clauses of the <code>ORDER BY</code> 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.<ref name="nullversusnull"/>