Null (SQL): Difference between revisions

Revision as of 15:41, 7 November 2024

Revision as of 15:49, 7 November 2024

Line 621:

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

The principle that Nulls are 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, likesuch as union orand 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 sortsorting 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"/>

==Effect on index operation==