Null (SQL): Difference between revisions

Revision as of 06:52, 14 December 2020

Revision as of 13:44, 14 January 2021

Line 626:

==Effect on index operation==

Some SQL products do not index keys containing NULLs. For instance, [[PostgreSQL]] versions prior to 8.3 did not, with the documentation for a [[B-tree]] index stating that<ref>{{cite web| url=http://www.postgresql.org/docs/8.0/interactive/indexes-types.html| title=PostgreSQL 8.0.14 Documentation: Index Types |publisher=PostgreSQL |accessdateaccess-date= 6 November 2008}}</ref>

{{quote|

B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators: < ≤ {{=}} ≥ >

Line 633:

}}

In cases where the index enforces uniqueness, NULLs are excluded from the index and uniqueness is not enforced between NULLs. Again, quoting from the [[PostgreSQL]] documentation:<ref>{{cite web| url=http://www.postgresql.org/docs/8.0/interactive/indexes-unique.html| title=PostgreSQL 8.0.14 Documentation: Unique Indexes |publisher=PostgreSQL |accessdateaccess-date=November 6, 2008}}</ref>

{{quote|

When an index is declared unique, multiple table rows with equal indexed values will not be allowed. Nulls are not considered equal. A multicolumn unique index will only reject cases where all of the indexed columns are equal in two rows.

Line 640:

This is consistent with the [[SQL:2003]]-defined behavior of scalar Null comparisons.

Another method of indexing Nulls involves handling them as ''not distinct'' in accordance with the SQL:2003-defined behavior. For example, [[Microsoft SQL Server]] documentation states the following:<ref>{{cite web| url=http://msdn2.microsoft.com/en-us/library/ms175132.aspx| title=Creating Unique Indexes|date=September 2007 |publisher=PostfreSQL |accessdateaccess-date=November 6, 2008}}</ref>

{{quote|

For indexing purposes, NULLs compare as equal. Therefore, a unique index, or UNIQUE constraint, cannot be created if the keys are NULL in more than one row. Select columns that are defined as NOT NULL when columns for a unique index or unique constraint are chosen.

Line 782:

| title =The Third Manifesto

| url =http://www.thethirdmanifesto.com/

| accessdateaccess-date = May 29, 2007

}}</ref> pointing to inconsistencies and flaws in the implementation of SQL Null-handling (particularly in aggregate functions) as proof that the entire concept of Null is flawed and should be removed from the relational model.<ref name="askew-wall">

{{cite web

Line 789:

| title =The Askew Wall

| url =http://www.dcs.warwick.ac.uk/~hugh/TTM/TTM-TheAskewWall-printable.pdf

| accessdateaccess-date = May 29, 2007

}}</ref> Others, like author [[Fabian Pascal]], have stated a belief that "how the function calculation should treat missing values is not governed by the relational model."{{citation needed|date=November 2012}}

Line 807:

| publisher =Data Management Association, San Francisco Bay Area Chapter

| url =http://www.sfdama.org/ChrisDate_20070110.htm

| accessdateaccess-date = May 29, 2007

| archive-url = https://web.archive.org/web/20070519134146/http://www.sfdama.org/ChrisDate_20070110.htm

| archive-date = 2007-05-19