SQL syntax - Revision history

OAbot: Open access bot: url-access updated in citation with #oabot.

2025-05-24T20:43:20Z

Open access bot: url-access updated in citation with #oabot.

← Previous revision		Revision as of 20:43, 24 May 2025
Line 406:		Line 406:
	There are however disputes about the semantic interpretation of Nulls in SQL because of its treatment outside direct comparisons. As seen in the table above, direct equality comparisons between two NULLs in SQL (e.g. {{code\|NULL {{=}} NULL}}) return a truth value of Unknown. This is in line with the interpretation that Null does not have a value (and is not a member of any data domain) but is rather a placeholder or "mark" for missing information. However, the principle that two Nulls aren't equal to each other is effectively violated in the SQL specification for the {{code\|UNION}} and {{code\|INTERSECT}} operators, which do identify nulls with each other.<ref name="Meyden">[https://books.google.com/books?id=gF0b85IuqQwC&pg=PA344 Ron van der Meyden, "Logical approaches to incomplete information: a survey" in Chomicki, Jan; Saake, Gunter (Eds.) ''Logics for Databases and Information Systems'', Kluwer Academic Publishers] {{ISBN\|978-0-7923-8129-7}}, p. 344</ref> Consequently, these [[Set operations (SQL)\|set operations in SQL]] may produce results not representing sure information, unlike operations involving explicit comparisons with NULL (e.g. those in a {{code\|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".<ref name="Klein" /> However, computer-science professor Ron van der Meyden concluded that "The inconsistencies in the SQL standard mean that it is not possible to ascribe any intuitive logical semantics to the treatment of nulls in SQL."<ref name="Meyden" />		There are however disputes about the semantic interpretation of Nulls in SQL because of its treatment outside direct comparisons. As seen in the table above, direct equality comparisons between two NULLs in SQL (e.g. {{code\|NULL {{=}} NULL}}) return a truth value of Unknown. This is in line with the interpretation that Null does not have a value (and is not a member of any data domain) but is rather a placeholder or "mark" for missing information. However, the principle that two Nulls aren't equal to each other is effectively violated in the SQL specification for the {{code\|UNION}} and {{code\|INTERSECT}} operators, which do identify nulls with each other.<ref name="Meyden">[https://books.google.com/books?id=gF0b85IuqQwC&pg=PA344 Ron van der Meyden, "Logical approaches to incomplete information: a survey" in Chomicki, Jan; Saake, Gunter (Eds.) ''Logics for Databases and Information Systems'', Kluwer Academic Publishers] {{ISBN\|978-0-7923-8129-7}}, p. 344</ref> Consequently, these [[Set operations (SQL)\|set operations in SQL]] may produce results not representing sure information, unlike operations involving explicit comparisons with NULL (e.g. those in a {{code\|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".<ref name="Klein" /> However, computer-science professor Ron van der Meyden concluded that "The inconsistencies in the SQL standard mean that it is not possible to ascribe any intuitive logical semantics to the treatment of nulls in SQL."<ref name="Meyden" />

	Additionally, because SQL operators return Unknown when comparing anything with Null directly, SQL provides two Null-specific comparison predicates: {{code\|IS NULL}} and {{code\|IS NOT NULL}} test whether data is or is not Null.<ref name="SQL2003-Part2-Sec87">{{cite book \| last = ISO/IEC \| title = ISO/IEC 9075-2:2003, "SQL/Foundation" \| publisher = ISO/IEC }}</ref> SQL does not explicitly support [[universal quantification]], and must work it out as a negated [[existential quantification]].<ref name="Negri">{{cite journal\|title=Semantics and problems of universal quantification in SQL \|doi=10.1093/comjnl/32.1.90 \|journal=The Computer Journal \|date= February 1989\|volume=32 \|issue=1 \|pages=90–91 \|last1=Negri \|first1=M. \|last2=Pelagatti \|first2=G. \|last3=Sbattella \|first3=L. \|doi-access=free }}</ref><ref name="Fratarcangeli">{{cite journal\|url=https://dl.acm.org/doi/10.1145/126482.126484 \|title=Technique for universal quantification in SQL \|doi=10.1145/126482.126484 \|journal=ACM SIGMOD Record \|year = 1991\|access-date=2024-11-07\|last1 = Fratarcangeli\|first1 = Claudio\|volume = 20\|issue = 3\|pages = 16–24\|s2cid=18326990 \|doi-access=free }}</ref><ref name="kawash">[https://www.thefreelibrary.com/Complex+quantification+in+Structured+Query+Language+(SQL):+a+tutorial...-a0119901477 Kawash, Jalal (2004) Complex quantification in Structured Query Language (SQL): a tutorial using relational calculus]; ''Journal of Computers in Mathematics and Science Teaching'' {{ISSN\|0731-9258}} Volume 23, Issue 2, 2004 AACE Norfolk, Virginia. Thefreelibrary.com</ref> There is also the {{code\|<row value expression> IS DISTINCT FROM <row value expression>}} infixed comparison operator, which returns TRUE unless both operands are equal or both are NULL. Likewise, IS NOT DISTINCT FROM is defined as {{code\|NOT (<row value expression> IS DISTINCT FROM <row value expression>)}}. [[SQL:1999]] also introduced {{code\|BOOLEAN}} type variables, which according to the standard can also hold Unknown values if it is nullable. In practice, a number of systems (e.g. [[PostgreSQL]]) implement the BOOLEAN Unknown as a BOOLEAN NULL, which the standard says that the NULL BOOLEAN and UNKNOWN "may be used interchangeably to mean exactly the same thing".<ref>{{cite book\|author=C. Date\|title=SQL and Relational Theory: How to Write Accurate SQL Code\|url=https://books.google.com/books?id=Ew06OZtjuJEC&pg=PA83\|year=2011\|publisher=O'Reilly Media, Inc.\|isbn=978-1-4493-1640-2\|page=83}}</ref><ref>ISO/IEC 9075-2:2011 §4.5</ref>		Additionally, because SQL operators return Unknown when comparing anything with Null directly, SQL provides two Null-specific comparison predicates: {{code\|IS NULL}} and {{code\|IS NOT NULL}} test whether data is or is not Null.<ref name="SQL2003-Part2-Sec87">{{cite book \| last = ISO/IEC \| title = ISO/IEC 9075-2:2003, "SQL/Foundation" \| publisher = ISO/IEC }}</ref> SQL does not explicitly support [[universal quantification]], and must work it out as a negated [[existential quantification]].<ref name="Negri">{{cite journal\|title=Semantics and problems of universal quantification in SQL \|doi=10.1093/comjnl/32.1.90 \|journal=The Computer Journal \|date= February 1989\|volume=32 \|issue=1 \|pages=90–91 \|last1=Negri \|first1=M. \|last2=Pelagatti \|first2=G. \|last3=Sbattella \|first3=L. \|doi-access=free }}</ref><ref name="Fratarcangeli">{{cite journal\|url=https://dl.acm.org/doi/10.1145/126482.126484 \|title=Technique for universal quantification in SQL \|doi=10.1145/126482.126484 \|journal=ACM SIGMOD Record \|year = 1991\|access-date=2024-11-07\|last1 = Fratarcangeli\|first1 = Claudio\|volume = 20\|issue = 3\|pages = 16–24\|s2cid=18326990 \|doi-access=free \|url-access=subscription}}</ref><ref name="kawash">[https://www.thefreelibrary.com/Complex+quantification+in+Structured+Query+Language+(SQL):+a+tutorial...-a0119901477 Kawash, Jalal (2004) Complex quantification in Structured Query Language (SQL): a tutorial using relational calculus]; ''Journal of Computers in Mathematics and Science Teaching'' {{ISSN\|0731-9258}} Volume 23, Issue 2, 2004 AACE Norfolk, Virginia. Thefreelibrary.com</ref> There is also the {{code\|<row value expression> IS DISTINCT FROM <row value expression>}} infixed comparison operator, which returns TRUE unless both operands are equal or both are NULL. Likewise, IS NOT DISTINCT FROM is defined as {{code\|NOT (<row value expression> IS DISTINCT FROM <row value expression>)}}. [[SQL:1999]] also introduced {{code\|BOOLEAN}} type variables, which according to the standard can also hold Unknown values if it is nullable. In practice, a number of systems (e.g. [[PostgreSQL]]) implement the BOOLEAN Unknown as a BOOLEAN NULL, which the standard says that the NULL BOOLEAN and UNKNOWN "may be used interchangeably to mean exactly the same thing".<ref>{{cite book\|author=C. Date\|title=SQL and Relational Theory: How to Write Accurate SQL Code\|url=https://books.google.com/books?id=Ew06OZtjuJEC&pg=PA83\|year=2011\|publisher=O'Reilly Media, Inc.\|isbn=978-1-4493-1640-2\|page=83}}</ref><ref>ISO/IEC 9075-2:2011 §4.5</ref>

	=== Data manipulation ===		=== Data manipulation ===

Asilvering: Removing link(s) Wikipedia:Articles for deletion/Where (SQL) closed as delete (XFDcloser)

2025-01-25T20:59:17Z

Removing link(s) Wikipedia:Articles for deletion/Where (SQL) closed as delete (XFDcloser)

← Previous revision		Revision as of 20:59, 25 January 2025
Line 157:		Line 157:

	* The <code>[[From (SQL)\|FROM]]</code> clause, which indicates the table(s) to retrieve data from. The {{code\|FROM}} clause can include optional <code>[[Join (SQL)\|JOIN]]</code> subclauses to specify the rules for joining tables.		* The <code>[[From (SQL)\|FROM]]</code> clause, which indicates the table(s) to retrieve data from. The {{code\|FROM}} clause can include optional <code>[[Join (SQL)\|JOIN]]</code> subclauses to specify the rules for joining tables.
	* The <code>~~[[Where (SQL)\|~~WHERE]]</code> clause includes a comparison predicate, which restricts the rows returned by the query. The {{code\|WHERE}} clause eliminates all rows from the [[result set]] where the comparison predicate does not evaluate to True.		* The <code>WHERE</code> clause includes a comparison predicate, which restricts the rows returned by the query. The {{code\|WHERE}} clause eliminates all rows from the [[result set]] where the comparison predicate does not evaluate to True.
	* The <code>[[Group by (SQL)\|GROUP BY]]</code> clause projects rows having common values into a smaller set of rows.{{Clarify\|reason=\|date=September 2020}} {{code\|GROUP BY}} is often used in conjunction with SQL aggregation functions or to eliminate duplicate rows from a result set. The {{code\|WHERE}} clause is applied before the {{code\|GROUP BY}} clause.		* The <code>[[Group by (SQL)\|GROUP BY]]</code> clause projects rows having common values into a smaller set of rows.{{Clarify\|reason=\|date=September 2020}} {{code\|GROUP BY}} is often used in conjunction with SQL aggregation functions or to eliminate duplicate rows from a result set. The {{code\|WHERE}} clause is applied before the {{code\|GROUP BY}} clause.
	* The <code>[[Having (SQL)\|HAVING]]</code> clause includes a predicate used to filter rows resulting from the {{code\|GROUP BY}} clause. Because it acts on the results of the {{code\|GROUP BY}} clause, aggregation functions can be used in the {{code\|HAVING}} clause predicate.		* The <code>[[Having (SQL)\|HAVING]]</code> clause includes a predicate used to filter rows resulting from the {{code\|GROUP BY}} clause. Because it acts on the results of the {{code\|GROUP BY}} clause, aggregation functions can be used in the {{code\|HAVING}} clause predicate.

TheJH: /* External links */ add archive-url to dead link

2025-01-22T18:04:51Z

External links: add archive-url to dead link

← Previous revision		Revision as of 18:04, 22 January 2025
Line 601:		Line 601:
	\|n=no\|s=no\|q=no}}		\|n=no\|s=no\|q=no}}
	* {{Citation \|url=https://standards.iso.org/iso-iec/9075/-2/ed-6/en/ISO_IEC_9075-2(E)_Foundation.bnf.xml \|contribution=Definition of SQL grammar \|title=ISO_IEC_9075-2(E)_Foundation.bnf.xml \|publisher=ISO Standards Maintenance Portal \|format=[[Backus–Naur form]] XML}}.		* {{Citation \|url=https://standards.iso.org/iso-iec/9075/-2/ed-6/en/ISO_IEC_9075-2(E)_Foundation.bnf.xml \|contribution=Definition of SQL grammar \|title=ISO_IEC_9075-2(E)_Foundation.bnf.xml \|publisher=ISO Standards Maintenance Portal \|format=[[Backus–Naur form]] XML}}.
	* {{Citation \|url=https://quickformatter.com/sql-formatter \|contribution=Online SQL Formating Utility \|title=Online SQL Formatter Utility}}.		* {{Citation \|url=https://quickformatter.com/sql-formatter \|contribution=Online SQL Formating Utility \|title=Online SQL Formatter Utility \|archive-url=https://web.archive.org/web/20240918172914/https://quickformatter.com/sql-formatter \|archive-date=2024-09-18 \|url-status=dead}}.

	{{SQL}}		{{SQL}}

Z. Patterson: The journal article listed is free to read on the journal's website.

2024-12-29T04:37:09Z

The journal article listed is free to read on the journal's website.

← Previous revision		Revision as of 04:37, 29 December 2024
Line 406:		Line 406:
	There are however disputes about the semantic interpretation of Nulls in SQL because of its treatment outside direct comparisons. As seen in the table above, direct equality comparisons between two NULLs in SQL (e.g. {{code\|NULL {{=}} NULL}}) return a truth value of Unknown. This is in line with the interpretation that Null does not have a value (and is not a member of any data domain) but is rather a placeholder or "mark" for missing information. However, the principle that two Nulls aren't equal to each other is effectively violated in the SQL specification for the {{code\|UNION}} and {{code\|INTERSECT}} operators, which do identify nulls with each other.<ref name="Meyden">[https://books.google.com/books?id=gF0b85IuqQwC&pg=PA344 Ron van der Meyden, "Logical approaches to incomplete information: a survey" in Chomicki, Jan; Saake, Gunter (Eds.) ''Logics for Databases and Information Systems'', Kluwer Academic Publishers] {{ISBN\|978-0-7923-8129-7}}, p. 344</ref> Consequently, these [[Set operations (SQL)\|set operations in SQL]] may produce results not representing sure information, unlike operations involving explicit comparisons with NULL (e.g. those in a {{code\|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".<ref name="Klein" /> However, computer-science professor Ron van der Meyden concluded that "The inconsistencies in the SQL standard mean that it is not possible to ascribe any intuitive logical semantics to the treatment of nulls in SQL."<ref name="Meyden" />		There are however disputes about the semantic interpretation of Nulls in SQL because of its treatment outside direct comparisons. As seen in the table above, direct equality comparisons between two NULLs in SQL (e.g. {{code\|NULL {{=}} NULL}}) return a truth value of Unknown. This is in line with the interpretation that Null does not have a value (and is not a member of any data domain) but is rather a placeholder or "mark" for missing information. However, the principle that two Nulls aren't equal to each other is effectively violated in the SQL specification for the {{code\|UNION}} and {{code\|INTERSECT}} operators, which do identify nulls with each other.<ref name="Meyden">[https://books.google.com/books?id=gF0b85IuqQwC&pg=PA344 Ron van der Meyden, "Logical approaches to incomplete information: a survey" in Chomicki, Jan; Saake, Gunter (Eds.) ''Logics for Databases and Information Systems'', Kluwer Academic Publishers] {{ISBN\|978-0-7923-8129-7}}, p. 344</ref> Consequently, these [[Set operations (SQL)\|set operations in SQL]] may produce results not representing sure information, unlike operations involving explicit comparisons with NULL (e.g. those in a {{code\|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".<ref name="Klein" /> However, computer-science professor Ron van der Meyden concluded that "The inconsistencies in the SQL standard mean that it is not possible to ascribe any intuitive logical semantics to the treatment of nulls in SQL."<ref name="Meyden" />

	Additionally, because SQL operators return Unknown when comparing anything with Null directly, SQL provides two Null-specific comparison predicates: {{code\|IS NULL}} and {{code\|IS NOT NULL}} test whether data is or is not Null.<ref name="SQL2003-Part2-Sec87">{{cite book \| last = ISO/IEC \| title = ISO/IEC 9075-2:2003, "SQL/Foundation" \| publisher = ISO/IEC }}</ref> SQL does not explicitly support [[universal quantification]], and must work it out as a negated [[existential quantification]].<ref name="Negri">{{cite journal\|title=Semantics and problems of universal quantification in SQL \|doi=10.1093/comjnl/32.1.90 \|journal=The Computer Journal \|date= February 1989\|volume=32 \|issue=1 \|pages=90–91 \|last1=Negri \|first1=M. \|last2=Pelagatti \|first2=G. \|last3=Sbattella \|first3=L. \|doi-access=free }}</ref><ref name="Fratarcangeli">{{cite journal\|url=https://dl.acm.org/doi/10.1145/126482.126484 \|title=Technique for universal quantification in SQL \|doi=10.1145/126482.126484 \|journal=ACM SIGMOD Record \|year = 1991\|access-date=2024-11-07\|last1 = Fratarcangeli\|first1 = Claudio\|volume = 20\|issue = 3\|pages = 16–24\|s2cid=18326990 }}</ref><ref name="kawash">[https://www.thefreelibrary.com/Complex+quantification+in+Structured+Query+Language+(SQL):+a+tutorial...-a0119901477 Kawash, Jalal (2004) Complex quantification in Structured Query Language (SQL): a tutorial using relational calculus]; ''Journal of Computers in Mathematics and Science Teaching'' {{ISSN\|0731-9258}} Volume 23, Issue 2, 2004 AACE Norfolk, Virginia. Thefreelibrary.com</ref> There is also the {{code\|<row value expression> IS DISTINCT FROM <row value expression>}} infixed comparison operator, which returns TRUE unless both operands are equal or both are NULL. Likewise, IS NOT DISTINCT FROM is defined as {{code\|NOT (<row value expression> IS DISTINCT FROM <row value expression>)}}. [[SQL:1999]] also introduced {{code\|BOOLEAN}} type variables, which according to the standard can also hold Unknown values if it is nullable. In practice, a number of systems (e.g. [[PostgreSQL]]) implement the BOOLEAN Unknown as a BOOLEAN NULL, which the standard says that the NULL BOOLEAN and UNKNOWN "may be used interchangeably to mean exactly the same thing".<ref>{{cite book\|author=C. Date\|title=SQL and Relational Theory: How to Write Accurate SQL Code\|url=https://books.google.com/books?id=Ew06OZtjuJEC&pg=PA83\|year=2011\|publisher=O'Reilly Media, Inc.\|isbn=978-1-4493-1640-2\|page=83}}</ref><ref>ISO/IEC 9075-2:2011 §4.5</ref>		Additionally, because SQL operators return Unknown when comparing anything with Null directly, SQL provides two Null-specific comparison predicates: {{code\|IS NULL}} and {{code\|IS NOT NULL}} test whether data is or is not Null.<ref name="SQL2003-Part2-Sec87">{{cite book \| last = ISO/IEC \| title = ISO/IEC 9075-2:2003, "SQL/Foundation" \| publisher = ISO/IEC }}</ref> SQL does not explicitly support [[universal quantification]], and must work it out as a negated [[existential quantification]].<ref name="Negri">{{cite journal\|title=Semantics and problems of universal quantification in SQL \|doi=10.1093/comjnl/32.1.90 \|journal=The Computer Journal \|date= February 1989\|volume=32 \|issue=1 \|pages=90–91 \|last1=Negri \|first1=M. \|last2=Pelagatti \|first2=G. \|last3=Sbattella \|first3=L. \|doi-access=free }}</ref><ref name="Fratarcangeli">{{cite journal\|url=https://dl.acm.org/doi/10.1145/126482.126484 \|title=Technique for universal quantification in SQL \|doi=10.1145/126482.126484 \|journal=ACM SIGMOD Record \|year = 1991\|access-date=2024-11-07\|last1 = Fratarcangeli\|first1 = Claudio\|volume = 20\|issue = 3\|pages = 16–24\|s2cid=18326990 \|doi-access=free }}</ref><ref name="kawash">[https://www.thefreelibrary.com/Complex+quantification+in+Structured+Query+Language+(SQL):+a+tutorial...-a0119901477 Kawash, Jalal (2004) Complex quantification in Structured Query Language (SQL): a tutorial using relational calculus]; ''Journal of Computers in Mathematics and Science Teaching'' {{ISSN\|0731-9258}} Volume 23, Issue 2, 2004 AACE Norfolk, Virginia. Thefreelibrary.com</ref> There is also the {{code\|<row value expression> IS DISTINCT FROM <row value expression>}} infixed comparison operator, which returns TRUE unless both operands are equal or both are NULL. Likewise, IS NOT DISTINCT FROM is defined as {{code\|NOT (<row value expression> IS DISTINCT FROM <row value expression>)}}. [[SQL:1999]] also introduced {{code\|BOOLEAN}} type variables, which according to the standard can also hold Unknown values if it is nullable. In practice, a number of systems (e.g. [[PostgreSQL]]) implement the BOOLEAN Unknown as a BOOLEAN NULL, which the standard says that the NULL BOOLEAN and UNKNOWN "may be used interchangeably to mean exactly the same thing".<ref>{{cite book\|author=C. Date\|title=SQL and Relational Theory: How to Write Accurate SQL Code\|url=https://books.google.com/books?id=Ew06OZtjuJEC&pg=PA83\|year=2011\|publisher=O'Reilly Media, Inc.\|isbn=978-1-4493-1640-2\|page=83}}</ref><ref>ISO/IEC 9075-2:2011 §4.5</ref>

	=== Data manipulation ===		=== Data manipulation ===

Citation bot: Altered url. URLs might have been anonymized. Add: archive-date, archive-url. Removed URL that duplicated identifier. Removed access-date with no URL. | Use this bot. Report bugs. | Suggested by Dominic3203 | Category:Query languages | #UCB_Category 56/66

2024-12-11T20:10:46Z

Altered url. URLs might have been anonymized. Add: archive-date, archive-url. Removed URL that duplicated identifier. Removed access-date with no URL. | Use this bot. Report bugs. | Suggested by Dominic3203 | Category:Query languages | #UCB_Category 56/66

← Previous revision		Revision as of 20:10, 11 December 2024
Line 304:		Line 304:
	The concept of [[Null (SQL)\|Null]] allows SQL to deal with missing information in the relational model. The word {{code\|NULL}} is a reserved keyword in SQL, used to identify the Null special marker. Comparisons with Null, for instance equality (=) in WHERE clauses, results in an Unknown truth value. In SELECT statements SQL returns only results for which the WHERE clause returns a value of True; i.e., it excludes results with values of False and also excludes those whose value is Unknown.		The concept of [[Null (SQL)\|Null]] allows SQL to deal with missing information in the relational model. The word {{code\|NULL}} is a reserved keyword in SQL, used to identify the Null special marker. Comparisons with Null, for instance equality (=) in WHERE clauses, results in an Unknown truth value. In SELECT statements SQL returns only results for which the WHERE clause returns a value of True; i.e., it excludes results with values of False and also excludes those whose value is Unknown.

	Along with True and False, the Unknown resulting from direct comparisons with Null thus brings a fragment of [[three-valued logic]] to SQL. The truth tables SQL uses for AND, OR, and NOT correspond to a common fragment of the Kleene and Lukasiewicz three-valued logic (which differ in their definition of implication, however SQL defines no such operation).<ref name="Klein">{{Cite book \| last1 = Hans-Joachim \| first1 = K. \| chapter = Null Values in Relational Databases and Sure Information Answers \| doi = 10.1007/3-540-36596-6_7 \| title = Semantics in Databases. Second International Workshop Dagstuhl Castle, Germany, January 7–12, 2001. Revised Papers \| series = Lecture Notes in Computer Science \| volume = 2582 \| pages = 119–138\| year = 2003 \| isbn = 978-3-540-00957-3 \| url = https://web.archive.org/web/20180707170027/http://www.is.informatik.uni-kiel.de/~hjk/sqlni.ps}}</ref>		Along with True and False, the Unknown resulting from direct comparisons with Null thus brings a fragment of [[three-valued logic]] to SQL. The truth tables SQL uses for AND, OR, and NOT correspond to a common fragment of the Kleene and Lukasiewicz three-valued logic (which differ in their definition of implication, however SQL defines no such operation).<ref name="Klein">{{Cite book \| last1 = Hans-Joachim \| first1 = K. \| chapter = Null Values in Relational Databases and Sure Information Answers \| doi = 10.1007/3-540-36596-6_7 \| title = Semantics in Databases. Second International Workshop Dagstuhl Castle, Germany, January 7–12, 2001. Revised Papers \| series = Lecture Notes in Computer Science \| volume = 2582 \| pages = 119–138\| year = 2003 \| isbn = 978-3-540-00957-3 \| url = http://www.is.informatik.uni-kiel.de/~hjk/sqlni.ps\| archive-url = https://web.archive.org/web/20180707170027/http://www.is.informatik.uni-kiel.de/~hjk/sqlni.ps \| archive-date = 7 July 2018 }}</ref>

	{\| border="0"		{\| border="0"
Line 406:		Line 406:
	There are however disputes about the semantic interpretation of Nulls in SQL because of its treatment outside direct comparisons. As seen in the table above, direct equality comparisons between two NULLs in SQL (e.g. {{code\|NULL {{=}} NULL}}) return a truth value of Unknown. This is in line with the interpretation that Null does not have a value (and is not a member of any data domain) but is rather a placeholder or "mark" for missing information. However, the principle that two Nulls aren't equal to each other is effectively violated in the SQL specification for the {{code\|UNION}} and {{code\|INTERSECT}} operators, which do identify nulls with each other.<ref name="Meyden">[https://books.google.com/books?id=gF0b85IuqQwC&pg=PA344 Ron van der Meyden, "Logical approaches to incomplete information: a survey" in Chomicki, Jan; Saake, Gunter (Eds.) ''Logics for Databases and Information Systems'', Kluwer Academic Publishers] {{ISBN\|978-0-7923-8129-7}}, p. 344</ref> Consequently, these [[Set operations (SQL)\|set operations in SQL]] may produce results not representing sure information, unlike operations involving explicit comparisons with NULL (e.g. those in a {{code\|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".<ref name="Klein" /> However, computer-science professor Ron van der Meyden concluded that "The inconsistencies in the SQL standard mean that it is not possible to ascribe any intuitive logical semantics to the treatment of nulls in SQL."<ref name="Meyden" />		There are however disputes about the semantic interpretation of Nulls in SQL because of its treatment outside direct comparisons. As seen in the table above, direct equality comparisons between two NULLs in SQL (e.g. {{code\|NULL {{=}} NULL}}) return a truth value of Unknown. This is in line with the interpretation that Null does not have a value (and is not a member of any data domain) but is rather a placeholder or "mark" for missing information. However, the principle that two Nulls aren't equal to each other is effectively violated in the SQL specification for the {{code\|UNION}} and {{code\|INTERSECT}} operators, which do identify nulls with each other.<ref name="Meyden">[https://books.google.com/books?id=gF0b85IuqQwC&pg=PA344 Ron van der Meyden, "Logical approaches to incomplete information: a survey" in Chomicki, Jan; Saake, Gunter (Eds.) ''Logics for Databases and Information Systems'', Kluwer Academic Publishers] {{ISBN\|978-0-7923-8129-7}}, p. 344</ref> Consequently, these [[Set operations (SQL)\|set operations in SQL]] may produce results not representing sure information, unlike operations involving explicit comparisons with NULL (e.g. those in a {{code\|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".<ref name="Klein" /> However, computer-science professor Ron van der Meyden concluded that "The inconsistencies in the SQL standard mean that it is not possible to ascribe any intuitive logical semantics to the treatment of nulls in SQL."<ref name="Meyden" />

	Additionally, because SQL operators return Unknown when comparing anything with Null directly, SQL provides two Null-specific comparison predicates: {{code\|IS NULL}} and {{code\|IS NOT NULL}} test whether data is or is not Null.<ref name="SQL2003-Part2-Sec87">{{cite book \| last = ISO/IEC \| title = ISO/IEC 9075-2:2003, "SQL/Foundation" \| publisher = ISO/IEC }}</ref> SQL does not explicitly support [[universal quantification]], and must work it out as a negated [[existential quantification]].<ref name="Negri">{{cite journal~~\|url=https://dl.acm.org/doi/10.1093/comjnl/32.1.90~~ \|title=Semantics and problems of universal quantification in SQL \|doi=10.1093/comjnl/32.1.90 \|journal=The Computer Journal \|date= February 1989\|volume=32 \|issue=1 \|pages=90–91 ~~\|access-date=2024-11-07~~\|last1=Negri \|first1=M. \|last2=Pelagatti \|first2=G. \|last3=Sbattella \|first3=L. \|doi-access=free }}</ref><ref name="Fratarcangeli">{{cite journal\|url=https://dl.acm.org/doi/10.1145/126482.126484 \|title=Technique for universal quantification in SQL \|doi=10.1145/126482.126484 \|journal=ACM SIGMOD Record \|year = 1991\|access-date=2024-11-07\|last1 = Fratarcangeli\|first1 = Claudio\|volume = 20\|issue = 3\|pages = 16–24\|s2cid=18326990 }}</ref><ref name="kawash">[https://www.thefreelibrary.com/Complex+quantification+in+Structured+Query+Language+(SQL):+a+tutorial...-a0119901477 Kawash, Jalal (2004) Complex quantification in Structured Query Language (SQL): a tutorial using relational calculus]; ''Journal of Computers in Mathematics and Science Teaching'' {{ISSN\|0731-9258}} Volume 23, Issue 2, 2004 AACE Norfolk, Virginia. Thefreelibrary.com</ref> There is also the {{code\|<row value expression> IS DISTINCT FROM <row value expression>}} infixed comparison operator, which returns TRUE unless both operands are equal or both are NULL. Likewise, IS NOT DISTINCT FROM is defined as {{code\|NOT (<row value expression> IS DISTINCT FROM <row value expression>)}}. [[SQL:1999]] also introduced {{code\|BOOLEAN}} type variables, which according to the standard can also hold Unknown values if it is nullable. In practice, a number of systems (e.g. [[PostgreSQL]]) implement the BOOLEAN Unknown as a BOOLEAN NULL, which the standard says that the NULL BOOLEAN and UNKNOWN "may be used interchangeably to mean exactly the same thing".<ref>{{cite book\|author=C. Date\|title=SQL and Relational Theory: How to Write Accurate SQL Code\|url=https://books.google.com/books?id=Ew06OZtjuJEC&pg=PA83\|year=2011\|publisher=O'Reilly Media, Inc.\|isbn=978-1-4493-1640-2\|page=83}}</ref><ref>ISO/IEC 9075-2:2011 §4.5</ref>		Additionally, because SQL operators return Unknown when comparing anything with Null directly, SQL provides two Null-specific comparison predicates: {{code\|IS NULL}} and {{code\|IS NOT NULL}} test whether data is or is not Null.<ref name="SQL2003-Part2-Sec87">{{cite book \| last = ISO/IEC \| title = ISO/IEC 9075-2:2003, "SQL/Foundation" \| publisher = ISO/IEC }}</ref> SQL does not explicitly support [[universal quantification]], and must work it out as a negated [[existential quantification]].<ref name="Negri">{{cite journal\|title=Semantics and problems of universal quantification in SQL \|doi=10.1093/comjnl/32.1.90 \|journal=The Computer Journal \|date= February 1989\|volume=32 \|issue=1 \|pages=90–91 \|last1=Negri \|first1=M. \|last2=Pelagatti \|first2=G. \|last3=Sbattella \|first3=L. \|doi-access=free }}</ref><ref name="Fratarcangeli">{{cite journal\|url=https://dl.acm.org/doi/10.1145/126482.126484 \|title=Technique for universal quantification in SQL \|doi=10.1145/126482.126484 \|journal=ACM SIGMOD Record \|year = 1991\|access-date=2024-11-07\|last1 = Fratarcangeli\|first1 = Claudio\|volume = 20\|issue = 3\|pages = 16–24\|s2cid=18326990 }}</ref><ref name="kawash">[https://www.thefreelibrary.com/Complex+quantification+in+Structured+Query+Language+(SQL):+a+tutorial...-a0119901477 Kawash, Jalal (2004) Complex quantification in Structured Query Language (SQL): a tutorial using relational calculus]; ''Journal of Computers in Mathematics and Science Teaching'' {{ISSN\|0731-9258}} Volume 23, Issue 2, 2004 AACE Norfolk, Virginia. Thefreelibrary.com</ref> There is also the {{code\|<row value expression> IS DISTINCT FROM <row value expression>}} infixed comparison operator, which returns TRUE unless both operands are equal or both are NULL. Likewise, IS NOT DISTINCT FROM is defined as {{code\|NOT (<row value expression> IS DISTINCT FROM <row value expression>)}}. [[SQL:1999]] also introduced {{code\|BOOLEAN}} type variables, which according to the standard can also hold Unknown values if it is nullable. In practice, a number of systems (e.g. [[PostgreSQL]]) implement the BOOLEAN Unknown as a BOOLEAN NULL, which the standard says that the NULL BOOLEAN and UNKNOWN "may be used interchangeably to mean exactly the same thing".<ref>{{cite book\|author=C. Date\|title=SQL and Relational Theory: How to Write Accurate SQL Code\|url=https://books.google.com/books?id=Ew06OZtjuJEC&pg=PA83\|year=2011\|publisher=O'Reilly Media, Inc.\|isbn=978-1-4493-1640-2\|page=83}}</ref><ref>ISO/IEC 9075-2:2011 §4.5</ref>

	=== Data manipulation ===		=== Data manipulation ===

IznoRepeat: add WP:TEMPLATECAT to remove from template; genfixes, added Empty section (1) tag

2024-12-05T20:42:07Z

add WP:TEMPLATECAT to remove from template; genfixes, added Empty section (1) tag

← Previous revision		Revision as of 20:42, 5 December 2024
Line 591:		Line 591:
	</syntaxhighlight>		</syntaxhighlight>

	== ~~Notes~~ ==		==References==
		⚫	{{Reflist}}

		⚫	==External links==
	{{Sister project links		{{Sister project links
	\|commons=Category:Structured Query Language		\|commons=Category:Structured Query Language
Line 598:		Line 600:
	\|b=Structured Query Language		\|b=Structured Query Language
	\|n=no\|s=no\|q=no}}		\|n=no\|s=no\|q=no}}

	==References==
⚫	{{Reflist}}

⚫	==External links==
	* {{Citation \|url=https://standards.iso.org/iso-iec/9075/-2/ed-6/en/ISO_IEC_9075-2(E)_Foundation.bnf.xml \|contribution=Definition of SQL grammar \|title=ISO_IEC_9075-2(E)_Foundation.bnf.xml \|publisher=ISO Standards Maintenance Portal \|format=[[Backus–Naur form]] XML}}.		* {{Citation \|url=https://standards.iso.org/iso-iec/9075/-2/ed-6/en/ISO_IEC_9075-2(E)_Foundation.bnf.xml \|contribution=Definition of SQL grammar \|title=ISO_IEC_9075-2(E)_Foundation.bnf.xml \|publisher=ISO Standards Maintenance Portal \|format=[[Backus–Naur form]] XML}}.
	* {{Citation \|url=https://quickformatter.com/sql-formatter \|contribution=Online SQL Formating Utility \|title=Online SQL Formatter Utility}}.		* {{Citation \|url=https://quickformatter.com/sql-formatter \|contribution=Online SQL Formating Utility \|title=Online SQL Formatter Utility}}.


	{{SQL}}		{{SQL}}
Line 619:		Line 615:
	[[Category:Data modeling languages]]		[[Category:Data modeling languages]]
	[[Category:Relational database management systems]]		[[Category:Relational database management systems]]
			[[Category:Database management systems]]

2600:6C5E:1F00:1489:0:0:0:19F2 at 08:46, 7 November 2024

2024-11-07T08:46:13Z

← Previous revision		Revision as of 08:46, 7 November 2024
Line 150:		Line 150:
	== Queries ==		== Queries ==

	The most common operation in SQL, the query, makes use of the declarative <code>[[Select (SQL)\|SELECT]]</code> statement. {{code\|SELECT}} retrieves data from one or more [[Table (database)\|table]]s, or expressions. Standard {{code\|SELECT}} statements have no persistent effects on the database. Some non-standard implementations of {{code\|SELECT}} can have persistent effects, such as the {{code\|SELECT INTO}} syntax provided in some databases.<ref name="ms-sql-select-into">{{cite ~~book~~ \| ~~chapter~~ = ~~Transact~~-~~SQL Reference~~ \| title = ~~SQL~~ ~~Server~~ ~~Language Reference \| series =~~ SQL ~~Server 2005 Books Online~~ \| ~~publisher~~ = ~~Microsoft~~ \| date = ~~2007~~-09-15 \| ~~url = http://msdn.microsoft.com/en-us/library/ms188029.aspx \|~~ access-date = ~~2007~~-06-17 }}</ref>		The most common operation in SQL, the query, makes use of the declarative <code>[[Select (SQL)\|SELECT]]</code> statement. {{code\|SELECT}} retrieves data from one or more [[Table (database)\|table]]s, or expressions. Standard {{code\|SELECT}} statements have no persistent effects on the database. Some non-standard implementations of {{code\|SELECT}} can have persistent effects, such as the {{code\|SELECT INTO}} syntax provided in some databases.<ref name="ms-sql-select-into">{{cite web\|url=https://learn.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql\|title=SELECT - INTO Clause (Transact-SQL)\|website=learn.microsoft.com\|date=2023-05-23\|access-date=2024-11-07}}</ref>

	Queries allow the user to describe desired data, leaving the [[Database management system\|database management system (DBMS)]] to carry out [[query plan\|planning]], [[query optimizer\|optimizing]], and performing the physical operations necessary to produce that result as it chooses.		Queries allow the user to describe desired data, leaving the [[Database management system\|database management system (DBMS)]] to carry out [[query plan\|planning]], [[query optimizer\|optimizing]], and performing the physical operations necessary to produce that result as it chooses.
Line 304:		Line 304:
	The concept of [[Null (SQL)\|Null]] allows SQL to deal with missing information in the relational model. The word {{code\|NULL}} is a reserved keyword in SQL, used to identify the Null special marker. Comparisons with Null, for instance equality (=) in WHERE clauses, results in an Unknown truth value. In SELECT statements SQL returns only results for which the WHERE clause returns a value of True; i.e., it excludes results with values of False and also excludes those whose value is Unknown.		The concept of [[Null (SQL)\|Null]] allows SQL to deal with missing information in the relational model. The word {{code\|NULL}} is a reserved keyword in SQL, used to identify the Null special marker. Comparisons with Null, for instance equality (=) in WHERE clauses, results in an Unknown truth value. In SELECT statements SQL returns only results for which the WHERE clause returns a value of True; i.e., it excludes results with values of False and also excludes those whose value is Unknown.

	Along with True and False, the Unknown resulting from direct comparisons with Null thus brings a fragment of [[three-valued logic]] to SQL. The truth tables SQL uses for AND, OR, and NOT correspond to a common fragment of the Kleene and Lukasiewicz three-valued logic (which differ in their definition of implication, however SQL defines no such operation).<ref name="Klein">{{Cite book \| last1 = Hans-Joachim \| first1 = K. \| chapter = Null Values in Relational Databases and Sure Information Answers \| doi = 10.1007/3-540-36596-6_7 \| title = Semantics in Databases. Second International Workshop Dagstuhl Castle, Germany, January 7–12, 2001. Revised Papers \| series = Lecture Notes in Computer Science \| volume = 2582 \| pages = 119–138\| year = 2003 \| isbn = 978-3-540-00957-3 \| url = http://www.is.informatik.uni-kiel.de/~hjk/sqlni.ps}}</ref>		Along with True and False, the Unknown resulting from direct comparisons with Null thus brings a fragment of [[three-valued logic]] to SQL. The truth tables SQL uses for AND, OR, and NOT correspond to a common fragment of the Kleene and Lukasiewicz three-valued logic (which differ in their definition of implication, however SQL defines no such operation).<ref name="Klein">{{Cite book \| last1 = Hans-Joachim \| first1 = K. \| chapter = Null Values in Relational Databases and Sure Information Answers \| doi = 10.1007/3-540-36596-6_7 \| title = Semantics in Databases. Second International Workshop Dagstuhl Castle, Germany, January 7–12, 2001. Revised Papers \| series = Lecture Notes in Computer Science \| volume = 2582 \| pages = 119–138\| year = 2003 \| isbn = 978-3-540-00957-3 \| url = https://web.archive.org/web/20180707170027/http://www.is.informatik.uni-kiel.de/~hjk/sqlni.ps}}</ref>

	{\| border="0"		{\| border="0"
Line 406:		Line 406:
	There are however disputes about the semantic interpretation of Nulls in SQL because of its treatment outside direct comparisons. As seen in the table above, direct equality comparisons between two NULLs in SQL (e.g. {{code\|NULL {{=}} NULL}}) return a truth value of Unknown. This is in line with the interpretation that Null does not have a value (and is not a member of any data domain) but is rather a placeholder or "mark" for missing information. However, the principle that two Nulls aren't equal to each other is effectively violated in the SQL specification for the {{code\|UNION}} and {{code\|INTERSECT}} operators, which do identify nulls with each other.<ref name="Meyden">[https://books.google.com/books?id=gF0b85IuqQwC&pg=PA344 Ron van der Meyden, "Logical approaches to incomplete information: a survey" in Chomicki, Jan; Saake, Gunter (Eds.) ''Logics for Databases and Information Systems'', Kluwer Academic Publishers] {{ISBN\|978-0-7923-8129-7}}, p. 344</ref> Consequently, these [[Set operations (SQL)\|set operations in SQL]] may produce results not representing sure information, unlike operations involving explicit comparisons with NULL (e.g. those in a {{code\|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".<ref name="Klein" /> However, computer-science professor Ron van der Meyden concluded that "The inconsistencies in the SQL standard mean that it is not possible to ascribe any intuitive logical semantics to the treatment of nulls in SQL."<ref name="Meyden" />		There are however disputes about the semantic interpretation of Nulls in SQL because of its treatment outside direct comparisons. As seen in the table above, direct equality comparisons between two NULLs in SQL (e.g. {{code\|NULL {{=}} NULL}}) return a truth value of Unknown. This is in line with the interpretation that Null does not have a value (and is not a member of any data domain) but is rather a placeholder or "mark" for missing information. However, the principle that two Nulls aren't equal to each other is effectively violated in the SQL specification for the {{code\|UNION}} and {{code\|INTERSECT}} operators, which do identify nulls with each other.<ref name="Meyden">[https://books.google.com/books?id=gF0b85IuqQwC&pg=PA344 Ron van der Meyden, "Logical approaches to incomplete information: a survey" in Chomicki, Jan; Saake, Gunter (Eds.) ''Logics for Databases and Information Systems'', Kluwer Academic Publishers] {{ISBN\|978-0-7923-8129-7}}, p. 344</ref> Consequently, these [[Set operations (SQL)\|set operations in SQL]] may produce results not representing sure information, unlike operations involving explicit comparisons with NULL (e.g. those in a {{code\|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".<ref name="Klein" /> However, computer-science professor Ron van der Meyden concluded that "The inconsistencies in the SQL standard mean that it is not possible to ascribe any intuitive logical semantics to the treatment of nulls in SQL."<ref name="Meyden" />

	Additionally, because SQL operators return Unknown when comparing anything with Null directly, SQL provides two Null-specific comparison predicates: {{code\|IS NULL}} and {{code\|IS NOT NULL}} test whether data is or is not Null.<ref name="SQL2003-Part2-Sec87">{{cite book \| last = ISO/IEC \| title = ISO/IEC 9075-2:2003, "SQL/Foundation" \| publisher = ISO/IEC }}</ref> SQL does not explicitly support [[universal quantification]], and must work it out as a negated [[existential quantification]].<ref name="Negri">{{cite journal\|url=~~http~~://~~portal~~.acm.org/~~citation~~.~~cfm?id=63224~~.~~68822&coll=GUIDE&dl=~~ \|title=Semantics and problems of universal quantification in SQL \|doi=10.1093/comjnl/32.1.90 \|journal=The Computer Journal \|date= February 1989\|volume=32 \|issue=1 \|pages=90–91 \|access-date=~~2017~~-01-16\|last1=Negri \|first1=M. \|last2=Pelagatti \|first2=G. \|last3=Sbattella \|first3=L. \|doi-access=free }}</ref><ref name="Fratarcangeli">{{cite journal\|url=~~http~~://~~portal~~.acm.org/~~citation~~.~~cfm?id=~~126482.126484~~&coll=GUIDE&dl=GUIDE&CFID=5934371&CFTOKEN=55309005~~ \|title=Technique for universal quantification in SQL \|doi=10.1145/126482.126484 \|journal=ACM SIGMOD Record \|year = 1991\|access-date=~~2017~~-01-16\|last1 = Fratarcangeli\|first1 = Claudio\|volume = 20\|issue = 3\|pages = 16–24\|s2cid=18326990 }}</ref><ref name="kawash">[~~http~~://www.thefreelibrary.com/Complex+quantification+in+Structured+Query+Language+(SQL):+a+tutorial...-a0119901477 Kawash, Jalal (2004) Complex quantification in Structured Query Language (SQL): a tutorial using relational calculus]; ''Journal of Computers in Mathematics and Science Teaching'' {{ISSN\|0731-9258}} Volume 23, Issue 2, 2004 AACE Norfolk, Virginia. Thefreelibrary.com</ref> There is also the {{code\|<row value expression> IS DISTINCT FROM <row value expression>}} infixed comparison operator, which returns TRUE unless both operands are equal or both are NULL. Likewise, IS NOT DISTINCT FROM is defined as {{code\|NOT (<row value expression> IS DISTINCT FROM <row value expression>)}}. [[SQL:1999]] also introduced {{code\|BOOLEAN}} type variables, which according to the standard can also hold Unknown values if it is nullable. In practice, a number of systems (e.g. [[PostgreSQL]]) implement the BOOLEAN Unknown as a BOOLEAN NULL, which the standard says that the NULL BOOLEAN and UNKNOWN "may be used interchangeably to mean exactly the same thing".<ref>{{cite book\|author=C. Date\|title=SQL and Relational Theory: How to Write Accurate SQL Code\|url=https://books.google.com/books?id=Ew06OZtjuJEC&pg=PA83\|year=2011\|publisher=O'Reilly Media, Inc.\|isbn=978-1-4493-1640-2\|page=83}}</ref><ref>ISO/IEC 9075-2:2011 §4.5</ref>		Additionally, because SQL operators return Unknown when comparing anything with Null directly, SQL provides two Null-specific comparison predicates: {{code\|IS NULL}} and {{code\|IS NOT NULL}} test whether data is or is not Null.<ref name="SQL2003-Part2-Sec87">{{cite book \| last = ISO/IEC \| title = ISO/IEC 9075-2:2003, "SQL/Foundation" \| publisher = ISO/IEC }}</ref> SQL does not explicitly support [[universal quantification]], and must work it out as a negated [[existential quantification]].<ref name="Negri">{{cite journal\|url=https://dl.acm.org/doi/10.1093/comjnl/32.1.90 \|title=Semantics and problems of universal quantification in SQL \|doi=10.1093/comjnl/32.1.90 \|journal=The Computer Journal \|date= February 1989\|volume=32 \|issue=1 \|pages=90–91 \|access-date=2024-11-07\|last1=Negri \|first1=M. \|last2=Pelagatti \|first2=G. \|last3=Sbattella \|first3=L. \|doi-access=free }}</ref><ref name="Fratarcangeli">{{cite journal\|url=https://dl.acm.org/doi/10.1145/126482.126484 \|title=Technique for universal quantification in SQL \|doi=10.1145/126482.126484 \|journal=ACM SIGMOD Record \|year = 1991\|access-date=2024-11-07\|last1 = Fratarcangeli\|first1 = Claudio\|volume = 20\|issue = 3\|pages = 16–24\|s2cid=18326990 }}</ref><ref name="kawash">[https://www.thefreelibrary.com/Complex+quantification+in+Structured+Query+Language+(SQL):+a+tutorial...-a0119901477 Kawash, Jalal (2004) Complex quantification in Structured Query Language (SQL): a tutorial using relational calculus]; ''Journal of Computers in Mathematics and Science Teaching'' {{ISSN\|0731-9258}} Volume 23, Issue 2, 2004 AACE Norfolk, Virginia. Thefreelibrary.com</ref> There is also the {{code\|<row value expression> IS DISTINCT FROM <row value expression>}} infixed comparison operator, which returns TRUE unless both operands are equal or both are NULL. Likewise, IS NOT DISTINCT FROM is defined as {{code\|NOT (<row value expression> IS DISTINCT FROM <row value expression>)}}. [[SQL:1999]] also introduced {{code\|BOOLEAN}} type variables, which according to the standard can also hold Unknown values if it is nullable. In practice, a number of systems (e.g. [[PostgreSQL]]) implement the BOOLEAN Unknown as a BOOLEAN NULL, which the standard says that the NULL BOOLEAN and UNKNOWN "may be used interchangeably to mean exactly the same thing".<ref>{{cite book\|author=C. Date\|title=SQL and Relational Theory: How to Write Accurate SQL Code\|url=https://books.google.com/books?id=Ew06OZtjuJEC&pg=PA83\|year=2011\|publisher=O'Reilly Media, Inc.\|isbn=978-1-4493-1640-2\|page=83}}</ref><ref>ISO/IEC 9075-2:2011 §4.5</ref>

	=== Data manipulation ===		=== Data manipulation ===

2402:E280:3E07:1D4:758E:8BFD:9BD5:93D7: /* External links */

2024-09-18T17:29:08Z

External links

← Previous revision		Revision as of 17:29, 18 September 2024
Line 604:		Line 604:
	==External links==		==External links==
	* {{Citation \|url=https://standards.iso.org/iso-iec/9075/-2/ed-6/en/ISO_IEC_9075-2(E)_Foundation.bnf.xml \|contribution=Definition of SQL grammar \|title=ISO_IEC_9075-2(E)_Foundation.bnf.xml \|publisher=ISO Standards Maintenance Portal \|format=[[Backus–Naur form]] XML}}.		* {{Citation \|url=https://standards.iso.org/iso-iec/9075/-2/ed-6/en/ISO_IEC_9075-2(E)_Foundation.bnf.xml \|contribution=Definition of SQL grammar \|title=ISO_IEC_9075-2(E)_Foundation.bnf.xml \|publisher=ISO Standards Maintenance Portal \|format=[[Backus–Naur form]] XML}}.
			* {{Citation \|url=https://quickformatter.com/sql-formatter \|contribution=Online SQL Formating Utility \|title=Online SQL Formatter Utility}}.

Ondra.pelech at 19:43, 6 August 2024

2024-08-06T19:43:01Z

← Previous revision		Revision as of 19:43, 6 August 2024
Line 599:		Line 599:
	\|n=no\|s=no\|q=no}}		\|n=no\|s=no\|q=no}}

	<References/>		==References==
			{{Reflist}}

			==External links==
			* {{Citation \|url=https://standards.iso.org/iso-iec/9075/-2/ed-6/en/ISO_IEC_9075-2(E)_Foundation.bnf.xml \|contribution=Definition of SQL grammar \|title=ISO_IEC_9075-2(E)_Foundation.bnf.xml \|publisher=ISO Standards Maintenance Portal \|format=[[Backus–Naur form]] XML}}.


	{{SQL}}		{{SQL}}

Nick Levine: Reverted 1 edit by 2406:7400:9A:240C:F111:C481:883A:7049 (talk): Promotional link

2024-06-16T12:56:06Z

Reverted 1 edit by 2406:7400:9A:240C:F111:C481:883A:7049 (talk): Promotional link

← Previous revision		Revision as of 12:56, 16 June 2024
Line 514:		Line 514:
	=== Data types ===		=== Data types ===

	Each column in an SQL table declares the type(s) that column may contain. ANSI SQL includes the following data types.<ref name="ISO/IEC">{{cite web \|~~last=Kasiraj~~ ~~\|first~~=~~Balaji~~ \|title=~~SQL~~ ~~Code~~ ~~beautifier~~ ~~for~~ ~~Analysts~~ ~~and~~ ~~Data~~ ~~Engineers.~~ ~~\|url=https~~:~~//sqlonline.in/~~ ~~\|url-status=live~~ ~~\|website=~~SQL ~~Online~~}}</ref>		Each column in an SQL table declares the type(s) that column may contain. ANSI SQL includes the following data types.<ref name="ISO/IEC">{{cite web \| url = https://www.iso.org/standard/63555.html \| title = ISO/IEC 9075-1:2016: Information technology – Database languages – SQL – Part 1: Framework (SQL/Framework) }}</ref>

	; Character strings and national character strings		; Character strings and national character strings

← Previous revision		Revision as of 20:59, 25 January 2025
Line 157:		Line 157:

	* The <code>[[From (SQL)\|FROM]]</code> clause, which indicates the table(s) to retrieve data from. The {{code\|FROM}} clause can include optional <code>[[Join (SQL)\|JOIN]]</code> subclauses to specify the rules for joining tables.		* The <code>[[From (SQL)\|FROM]]</code> clause, which indicates the table(s) to retrieve data from. The {{code\|FROM}} clause can include optional <code>[[Join (SQL)\|JOIN]]</code> subclauses to specify the rules for joining tables.
	* The <code>~~[[Where (SQL)\|~~WHERE]]</code> clause includes a comparison predicate, which restricts the rows returned by the query. The {{code\|WHERE}} clause eliminates all rows from the [[result set]] where the comparison predicate does not evaluate to True.		* The <code>WHERE</code> clause includes a comparison predicate, which restricts the rows returned by the query. The {{code\|WHERE}} clause eliminates all rows from the [[result set]] where the comparison predicate does not evaluate to True.
	* The <code>[[Group by (SQL)\|GROUP BY]]</code> clause projects rows having common values into a smaller set of rows.{{Clarify\|reason=\|date=September 2020}} {{code\|GROUP BY}} is often used in conjunction with SQL aggregation functions or to eliminate duplicate rows from a result set. The {{code\|WHERE}} clause is applied before the {{code\|GROUP BY}} clause.		* The <code>[[Group by (SQL)\|GROUP BY]]</code> clause projects rows having common values into a smaller set of rows.{{Clarify\|reason=\|date=September 2020}} {{code\|GROUP BY}} is often used in conjunction with SQL aggregation functions or to eliminate duplicate rows from a result set. The {{code\|WHERE}} clause is applied before the {{code\|GROUP BY}} clause.
	* The <code>[[Having (SQL)\|HAVING]]</code> clause includes a predicate used to filter rows resulting from the {{code\|GROUP BY}} clause. Because it acts on the results of the {{code\|GROUP BY}} clause, aggregation functions can be used in the {{code\|HAVING}} clause predicate.		* The <code>[[Having (SQL)\|HAVING]]</code> clause includes a predicate used to filter rows resulting from the {{code\|GROUP BY}} clause. Because it acts on the results of the {{code\|GROUP BY}} clause, aggregation functions can be used in the {{code\|HAVING}} clause predicate.

← Previous revision		Revision as of 20:10, 11 December 2024
Line 304:		Line 304:
	The concept of [[Null (SQL)\|Null]] allows SQL to deal with missing information in the relational model. The word {{code\|NULL}} is a reserved keyword in SQL, used to identify the Null special marker. Comparisons with Null, for instance equality (=) in WHERE clauses, results in an Unknown truth value. In SELECT statements SQL returns only results for which the WHERE clause returns a value of True; i.e., it excludes results with values of False and also excludes those whose value is Unknown.		The concept of [[Null (SQL)\|Null]] allows SQL to deal with missing information in the relational model. The word {{code\|NULL}} is a reserved keyword in SQL, used to identify the Null special marker. Comparisons with Null, for instance equality (=) in WHERE clauses, results in an Unknown truth value. In SELECT statements SQL returns only results for which the WHERE clause returns a value of True; i.e., it excludes results with values of False and also excludes those whose value is Unknown.

	Along with True and False, the Unknown resulting from direct comparisons with Null thus brings a fragment of [[three-valued logic]] to SQL. The truth tables SQL uses for AND, OR, and NOT correspond to a common fragment of the Kleene and Lukasiewicz three-valued logic (which differ in their definition of implication, however SQL defines no such operation).<ref name="Klein">{{Cite book \| last1 = Hans-Joachim \| first1 = K. \| chapter = Null Values in Relational Databases and Sure Information Answers \| doi = 10.1007/3-540-36596-6_7 \| title = Semantics in Databases. Second International Workshop Dagstuhl Castle, Germany, January 7–12, 2001. Revised Papers \| series = Lecture Notes in Computer Science \| volume = 2582 \| pages = 119–138\| year = 2003 \| isbn = 978-3-540-00957-3 \| url = https://web.archive.org/web/20180707170027/http://www.is.informatik.uni-kiel.de/~hjk/sqlni.ps}}</ref>		Along with True and False, the Unknown resulting from direct comparisons with Null thus brings a fragment of [[three-valued logic]] to SQL. The truth tables SQL uses for AND, OR, and NOT correspond to a common fragment of the Kleene and Lukasiewicz three-valued logic (which differ in their definition of implication, however SQL defines no such operation).<ref name="Klein">{{Cite book \| last1 = Hans-Joachim \| first1 = K. \| chapter = Null Values in Relational Databases and Sure Information Answers \| doi = 10.1007/3-540-36596-6_7 \| title = Semantics in Databases. Second International Workshop Dagstuhl Castle, Germany, January 7–12, 2001. Revised Papers \| series = Lecture Notes in Computer Science \| volume = 2582 \| pages = 119–138\| year = 2003 \| isbn = 978-3-540-00957-3 \| url = http://www.is.informatik.uni-kiel.de/~hjk/sqlni.ps\| archive-url = https://web.archive.org/web/20180707170027/http://www.is.informatik.uni-kiel.de/~hjk/sqlni.ps \| archive-date = 7 July 2018 }}</ref>

	{\| border="0"		{\| border="0"
Line 406:		Line 406:
	There are however disputes about the semantic interpretation of Nulls in SQL because of its treatment outside direct comparisons. As seen in the table above, direct equality comparisons between two NULLs in SQL (e.g. {{code\|NULL {{=}} NULL}}) return a truth value of Unknown. This is in line with the interpretation that Null does not have a value (and is not a member of any data domain) but is rather a placeholder or "mark" for missing information. However, the principle that two Nulls aren't equal to each other is effectively violated in the SQL specification for the {{code\|UNION}} and {{code\|INTERSECT}} operators, which do identify nulls with each other.<ref name="Meyden">[https://books.google.com/books?id=gF0b85IuqQwC&pg=PA344 Ron van der Meyden, "Logical approaches to incomplete information: a survey" in Chomicki, Jan; Saake, Gunter (Eds.) ''Logics for Databases and Information Systems'', Kluwer Academic Publishers] {{ISBN\|978-0-7923-8129-7}}, p. 344</ref> Consequently, these [[Set operations (SQL)\|set operations in SQL]] may produce results not representing sure information, unlike operations involving explicit comparisons with NULL (e.g. those in a {{code\|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".<ref name="Klein" /> However, computer-science professor Ron van der Meyden concluded that "The inconsistencies in the SQL standard mean that it is not possible to ascribe any intuitive logical semantics to the treatment of nulls in SQL."<ref name="Meyden" />		There are however disputes about the semantic interpretation of Nulls in SQL because of its treatment outside direct comparisons. As seen in the table above, direct equality comparisons between two NULLs in SQL (e.g. {{code\|NULL {{=}} NULL}}) return a truth value of Unknown. This is in line with the interpretation that Null does not have a value (and is not a member of any data domain) but is rather a placeholder or "mark" for missing information. However, the principle that two Nulls aren't equal to each other is effectively violated in the SQL specification for the {{code\|UNION}} and {{code\|INTERSECT}} operators, which do identify nulls with each other.<ref name="Meyden">[https://books.google.com/books?id=gF0b85IuqQwC&pg=PA344 Ron van der Meyden, "Logical approaches to incomplete information: a survey" in Chomicki, Jan; Saake, Gunter (Eds.) ''Logics for Databases and Information Systems'', Kluwer Academic Publishers] {{ISBN\|978-0-7923-8129-7}}, p. 344</ref> Consequently, these [[Set operations (SQL)\|set operations in SQL]] may produce results not representing sure information, unlike operations involving explicit comparisons with NULL (e.g. those in a {{code\|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".<ref name="Klein" /> However, computer-science professor Ron van der Meyden concluded that "The inconsistencies in the SQL standard mean that it is not possible to ascribe any intuitive logical semantics to the treatment of nulls in SQL."<ref name="Meyden" />

	Additionally, because SQL operators return Unknown when comparing anything with Null directly, SQL provides two Null-specific comparison predicates: {{code\|IS NULL}} and {{code\|IS NOT NULL}} test whether data is or is not Null.<ref name="SQL2003-Part2-Sec87">{{cite book \| last = ISO/IEC \| title = ISO/IEC 9075-2:2003, "SQL/Foundation" \| publisher = ISO/IEC }}</ref> SQL does not explicitly support [[universal quantification]], and must work it out as a negated [[existential quantification]].<ref name="Negri">{{cite journal~~\|url=https://dl.acm.org/doi/10.1093/comjnl/32.1.90~~ \|title=Semantics and problems of universal quantification in SQL \|doi=10.1093/comjnl/32.1.90 \|journal=The Computer Journal \|date= February 1989\|volume=32 \|issue=1 \|pages=90–91 ~~\|access-date=2024-11-07~~\|last1=Negri \|first1=M. \|last2=Pelagatti \|first2=G. \|last3=Sbattella \|first3=L. \|doi-access=free }}</ref><ref name="Fratarcangeli">{{cite journal\|url=https://dl.acm.org/doi/10.1145/126482.126484 \|title=Technique for universal quantification in SQL \|doi=10.1145/126482.126484 \|journal=ACM SIGMOD Record \|year = 1991\|access-date=2024-11-07\|last1 = Fratarcangeli\|first1 = Claudio\|volume = 20\|issue = 3\|pages = 16–24\|s2cid=18326990 }}</ref><ref name="kawash">[https://www.thefreelibrary.com/Complex+quantification+in+Structured+Query+Language+(SQL):+a+tutorial...-a0119901477 Kawash, Jalal (2004) Complex quantification in Structured Query Language (SQL): a tutorial using relational calculus]; ''Journal of Computers in Mathematics and Science Teaching'' {{ISSN\|0731-9258}} Volume 23, Issue 2, 2004 AACE Norfolk, Virginia. Thefreelibrary.com</ref> There is also the {{code\|<row value expression> IS DISTINCT FROM <row value expression>}} infixed comparison operator, which returns TRUE unless both operands are equal or both are NULL. Likewise, IS NOT DISTINCT FROM is defined as {{code\|NOT (<row value expression> IS DISTINCT FROM <row value expression>)}}. [[SQL:1999]] also introduced {{code\|BOOLEAN}} type variables, which according to the standard can also hold Unknown values if it is nullable. In practice, a number of systems (e.g. [[PostgreSQL]]) implement the BOOLEAN Unknown as a BOOLEAN NULL, which the standard says that the NULL BOOLEAN and UNKNOWN "may be used interchangeably to mean exactly the same thing".<ref>{{cite book\|author=C. Date\|title=SQL and Relational Theory: How to Write Accurate SQL Code\|url=https://books.google.com/books?id=Ew06OZtjuJEC&pg=PA83\|year=2011\|publisher=O'Reilly Media, Inc.\|isbn=978-1-4493-1640-2\|page=83}}</ref><ref>ISO/IEC 9075-2:2011 §4.5</ref>		Additionally, because SQL operators return Unknown when comparing anything with Null directly, SQL provides two Null-specific comparison predicates: {{code\|IS NULL}} and {{code\|IS NOT NULL}} test whether data is or is not Null.<ref name="SQL2003-Part2-Sec87">{{cite book \| last = ISO/IEC \| title = ISO/IEC 9075-2:2003, "SQL/Foundation" \| publisher = ISO/IEC }}</ref> SQL does not explicitly support [[universal quantification]], and must work it out as a negated [[existential quantification]].<ref name="Negri">{{cite journal\|title=Semantics and problems of universal quantification in SQL \|doi=10.1093/comjnl/32.1.90 \|journal=The Computer Journal \|date= February 1989\|volume=32 \|issue=1 \|pages=90–91 \|last1=Negri \|first1=M. \|last2=Pelagatti \|first2=G. \|last3=Sbattella \|first3=L. \|doi-access=free }}</ref><ref name="Fratarcangeli">{{cite journal\|url=https://dl.acm.org/doi/10.1145/126482.126484 \|title=Technique for universal quantification in SQL \|doi=10.1145/126482.126484 \|journal=ACM SIGMOD Record \|year = 1991\|access-date=2024-11-07\|last1 = Fratarcangeli\|first1 = Claudio\|volume = 20\|issue = 3\|pages = 16–24\|s2cid=18326990 }}</ref><ref name="kawash">[https://www.thefreelibrary.com/Complex+quantification+in+Structured+Query+Language+(SQL):+a+tutorial...-a0119901477 Kawash, Jalal (2004) Complex quantification in Structured Query Language (SQL): a tutorial using relational calculus]; ''Journal of Computers in Mathematics and Science Teaching'' {{ISSN\|0731-9258}} Volume 23, Issue 2, 2004 AACE Norfolk, Virginia. Thefreelibrary.com</ref> There is also the {{code\|<row value expression> IS DISTINCT FROM <row value expression>}} infixed comparison operator, which returns TRUE unless both operands are equal or both are NULL. Likewise, IS NOT DISTINCT FROM is defined as {{code\|NOT (<row value expression> IS DISTINCT FROM <row value expression>)}}. [[SQL:1999]] also introduced {{code\|BOOLEAN}} type variables, which according to the standard can also hold Unknown values if it is nullable. In practice, a number of systems (e.g. [[PostgreSQL]]) implement the BOOLEAN Unknown as a BOOLEAN NULL, which the standard says that the NULL BOOLEAN and UNKNOWN "may be used interchangeably to mean exactly the same thing".<ref>{{cite book\|author=C. Date\|title=SQL and Relational Theory: How to Write Accurate SQL Code\|url=https://books.google.com/books?id=Ew06OZtjuJEC&pg=PA83\|year=2011\|publisher=O'Reilly Media, Inc.\|isbn=978-1-4493-1640-2\|page=83}}</ref><ref>ISO/IEC 9075-2:2011 §4.5</ref>

	=== Data manipulation ===		=== Data manipulation ===

← Previous revision		Revision as of 08:46, 7 November 2024
Line 150:		Line 150:
	== Queries ==		== Queries ==

	The most common operation in SQL, the query, makes use of the declarative <code>[[Select (SQL)\|SELECT]]</code> statement. {{code\|SELECT}} retrieves data from one or more [[Table (database)\|table]]s, or expressions. Standard {{code\|SELECT}} statements have no persistent effects on the database. Some non-standard implementations of {{code\|SELECT}} can have persistent effects, such as the {{code\|SELECT INTO}} syntax provided in some databases.<ref name="ms-sql-select-into">{{cite ~~book~~ \| ~~chapter~~ = ~~Transact~~-~~SQL Reference~~ \| title = ~~SQL~~ ~~Server~~ ~~Language Reference \| series =~~ SQL ~~Server 2005 Books Online~~ \| ~~publisher~~ = ~~Microsoft~~ \| date = ~~2007~~-09-15 \| ~~url = http://msdn.microsoft.com/en-us/library/ms188029.aspx \|~~ access-date = ~~2007~~-06-17 }}</ref>		The most common operation in SQL, the query, makes use of the declarative <code>[[Select (SQL)\|SELECT]]</code> statement. {{code\|SELECT}} retrieves data from one or more [[Table (database)\|table]]s, or expressions. Standard {{code\|SELECT}} statements have no persistent effects on the database. Some non-standard implementations of {{code\|SELECT}} can have persistent effects, such as the {{code\|SELECT INTO}} syntax provided in some databases.<ref name="ms-sql-select-into">{{cite web\|url=https://learn.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql\|title=SELECT - INTO Clause (Transact-SQL)\|website=learn.microsoft.com\|date=2023-05-23\|access-date=2024-11-07}}</ref>

	Queries allow the user to describe desired data, leaving the [[Database management system\|database management system (DBMS)]] to carry out [[query plan\|planning]], [[query optimizer\|optimizing]], and performing the physical operations necessary to produce that result as it chooses.		Queries allow the user to describe desired data, leaving the [[Database management system\|database management system (DBMS)]] to carry out [[query plan\|planning]], [[query optimizer\|optimizing]], and performing the physical operations necessary to produce that result as it chooses.
Line 304:		Line 304:
	The concept of [[Null (SQL)\|Null]] allows SQL to deal with missing information in the relational model. The word {{code\|NULL}} is a reserved keyword in SQL, used to identify the Null special marker. Comparisons with Null, for instance equality (=) in WHERE clauses, results in an Unknown truth value. In SELECT statements SQL returns only results for which the WHERE clause returns a value of True; i.e., it excludes results with values of False and also excludes those whose value is Unknown.		The concept of [[Null (SQL)\|Null]] allows SQL to deal with missing information in the relational model. The word {{code\|NULL}} is a reserved keyword in SQL, used to identify the Null special marker. Comparisons with Null, for instance equality (=) in WHERE clauses, results in an Unknown truth value. In SELECT statements SQL returns only results for which the WHERE clause returns a value of True; i.e., it excludes results with values of False and also excludes those whose value is Unknown.

	Along with True and False, the Unknown resulting from direct comparisons with Null thus brings a fragment of [[three-valued logic]] to SQL. The truth tables SQL uses for AND, OR, and NOT correspond to a common fragment of the Kleene and Lukasiewicz three-valued logic (which differ in their definition of implication, however SQL defines no such operation).<ref name="Klein">{{Cite book \| last1 = Hans-Joachim \| first1 = K. \| chapter = Null Values in Relational Databases and Sure Information Answers \| doi = 10.1007/3-540-36596-6_7 \| title = Semantics in Databases. Second International Workshop Dagstuhl Castle, Germany, January 7–12, 2001. Revised Papers \| series = Lecture Notes in Computer Science \| volume = 2582 \| pages = 119–138\| year = 2003 \| isbn = 978-3-540-00957-3 \| url = http://www.is.informatik.uni-kiel.de/~hjk/sqlni.ps}}</ref>		Along with True and False, the Unknown resulting from direct comparisons with Null thus brings a fragment of [[three-valued logic]] to SQL. The truth tables SQL uses for AND, OR, and NOT correspond to a common fragment of the Kleene and Lukasiewicz three-valued logic (which differ in their definition of implication, however SQL defines no such operation).<ref name="Klein">{{Cite book \| last1 = Hans-Joachim \| first1 = K. \| chapter = Null Values in Relational Databases and Sure Information Answers \| doi = 10.1007/3-540-36596-6_7 \| title = Semantics in Databases. Second International Workshop Dagstuhl Castle, Germany, January 7–12, 2001. Revised Papers \| series = Lecture Notes in Computer Science \| volume = 2582 \| pages = 119–138\| year = 2003 \| isbn = 978-3-540-00957-3 \| url = https://web.archive.org/web/20180707170027/http://www.is.informatik.uni-kiel.de/~hjk/sqlni.ps}}</ref>

	{\| border="0"		{\| border="0"
Line 406:		Line 406:
	There are however disputes about the semantic interpretation of Nulls in SQL because of its treatment outside direct comparisons. As seen in the table above, direct equality comparisons between two NULLs in SQL (e.g. {{code\|NULL {{=}} NULL}}) return a truth value of Unknown. This is in line with the interpretation that Null does not have a value (and is not a member of any data domain) but is rather a placeholder or "mark" for missing information. However, the principle that two Nulls aren't equal to each other is effectively violated in the SQL specification for the {{code\|UNION}} and {{code\|INTERSECT}} operators, which do identify nulls with each other.<ref name="Meyden">[https://books.google.com/books?id=gF0b85IuqQwC&pg=PA344 Ron van der Meyden, "Logical approaches to incomplete information: a survey" in Chomicki, Jan; Saake, Gunter (Eds.) ''Logics for Databases and Information Systems'', Kluwer Academic Publishers] {{ISBN\|978-0-7923-8129-7}}, p. 344</ref> Consequently, these [[Set operations (SQL)\|set operations in SQL]] may produce results not representing sure information, unlike operations involving explicit comparisons with NULL (e.g. those in a {{code\|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".<ref name="Klein" /> However, computer-science professor Ron van der Meyden concluded that "The inconsistencies in the SQL standard mean that it is not possible to ascribe any intuitive logical semantics to the treatment of nulls in SQL."<ref name="Meyden" />		There are however disputes about the semantic interpretation of Nulls in SQL because of its treatment outside direct comparisons. As seen in the table above, direct equality comparisons between two NULLs in SQL (e.g. {{code\|NULL {{=}} NULL}}) return a truth value of Unknown. This is in line with the interpretation that Null does not have a value (and is not a member of any data domain) but is rather a placeholder or "mark" for missing information. However, the principle that two Nulls aren't equal to each other is effectively violated in the SQL specification for the {{code\|UNION}} and {{code\|INTERSECT}} operators, which do identify nulls with each other.<ref name="Meyden">[https://books.google.com/books?id=gF0b85IuqQwC&pg=PA344 Ron van der Meyden, "Logical approaches to incomplete information: a survey" in Chomicki, Jan; Saake, Gunter (Eds.) ''Logics for Databases and Information Systems'', Kluwer Academic Publishers] {{ISBN\|978-0-7923-8129-7}}, p. 344</ref> Consequently, these [[Set operations (SQL)\|set operations in SQL]] may produce results not representing sure information, unlike operations involving explicit comparisons with NULL (e.g. those in a {{code\|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".<ref name="Klein" /> However, computer-science professor Ron van der Meyden concluded that "The inconsistencies in the SQL standard mean that it is not possible to ascribe any intuitive logical semantics to the treatment of nulls in SQL."<ref name="Meyden" />

	Additionally, because SQL operators return Unknown when comparing anything with Null directly, SQL provides two Null-specific comparison predicates: {{code\|IS NULL}} and {{code\|IS NOT NULL}} test whether data is or is not Null.<ref name="SQL2003-Part2-Sec87">{{cite book \| last = ISO/IEC \| title = ISO/IEC 9075-2:2003, "SQL/Foundation" \| publisher = ISO/IEC }}</ref> SQL does not explicitly support [[universal quantification]], and must work it out as a negated [[existential quantification]].<ref name="Negri">{{cite journal\|url=~~http~~://~~portal~~.acm.org/~~citation~~.~~cfm?id=63224~~.~~68822&coll=GUIDE&dl=~~ \|title=Semantics and problems of universal quantification in SQL \|doi=10.1093/comjnl/32.1.90 \|journal=The Computer Journal \|date= February 1989\|volume=32 \|issue=1 \|pages=90–91 \|access-date=~~2017~~-01-16\|last1=Negri \|first1=M. \|last2=Pelagatti \|first2=G. \|last3=Sbattella \|first3=L. \|doi-access=free }}</ref><ref name="Fratarcangeli">{{cite journal\|url=~~http~~://~~portal~~.acm.org/~~citation~~.~~cfm?id=~~126482.126484~~&coll=GUIDE&dl=GUIDE&CFID=5934371&CFTOKEN=55309005~~ \|title=Technique for universal quantification in SQL \|doi=10.1145/126482.126484 \|journal=ACM SIGMOD Record \|year = 1991\|access-date=~~2017~~-01-16\|last1 = Fratarcangeli\|first1 = Claudio\|volume = 20\|issue = 3\|pages = 16–24\|s2cid=18326990 }}</ref><ref name="kawash">[~~http~~://www.thefreelibrary.com/Complex+quantification+in+Structured+Query+Language+(SQL):+a+tutorial...-a0119901477 Kawash, Jalal (2004) Complex quantification in Structured Query Language (SQL): a tutorial using relational calculus]; ''Journal of Computers in Mathematics and Science Teaching'' {{ISSN\|0731-9258}} Volume 23, Issue 2, 2004 AACE Norfolk, Virginia. Thefreelibrary.com</ref> There is also the {{code\|<row value expression> IS DISTINCT FROM <row value expression>}} infixed comparison operator, which returns TRUE unless both operands are equal or both are NULL. Likewise, IS NOT DISTINCT FROM is defined as {{code\|NOT (<row value expression> IS DISTINCT FROM <row value expression>)}}. [[SQL:1999]] also introduced {{code\|BOOLEAN}} type variables, which according to the standard can also hold Unknown values if it is nullable. In practice, a number of systems (e.g. [[PostgreSQL]]) implement the BOOLEAN Unknown as a BOOLEAN NULL, which the standard says that the NULL BOOLEAN and UNKNOWN "may be used interchangeably to mean exactly the same thing".<ref>{{cite book\|author=C. Date\|title=SQL and Relational Theory: How to Write Accurate SQL Code\|url=https://books.google.com/books?id=Ew06OZtjuJEC&pg=PA83\|year=2011\|publisher=O'Reilly Media, Inc.\|isbn=978-1-4493-1640-2\|page=83}}</ref><ref>ISO/IEC 9075-2:2011 §4.5</ref>		Additionally, because SQL operators return Unknown when comparing anything with Null directly, SQL provides two Null-specific comparison predicates: {{code\|IS NULL}} and {{code\|IS NOT NULL}} test whether data is or is not Null.<ref name="SQL2003-Part2-Sec87">{{cite book \| last = ISO/IEC \| title = ISO/IEC 9075-2:2003, "SQL/Foundation" \| publisher = ISO/IEC }}</ref> SQL does not explicitly support [[universal quantification]], and must work it out as a negated [[existential quantification]].<ref name="Negri">{{cite journal\|url=https://dl.acm.org/doi/10.1093/comjnl/32.1.90 \|title=Semantics and problems of universal quantification in SQL \|doi=10.1093/comjnl/32.1.90 \|journal=The Computer Journal \|date= February 1989\|volume=32 \|issue=1 \|pages=90–91 \|access-date=2024-11-07\|last1=Negri \|first1=M. \|last2=Pelagatti \|first2=G. \|last3=Sbattella \|first3=L. \|doi-access=free }}</ref><ref name="Fratarcangeli">{{cite journal\|url=https://dl.acm.org/doi/10.1145/126482.126484 \|title=Technique for universal quantification in SQL \|doi=10.1145/126482.126484 \|journal=ACM SIGMOD Record \|year = 1991\|access-date=2024-11-07\|last1 = Fratarcangeli\|first1 = Claudio\|volume = 20\|issue = 3\|pages = 16–24\|s2cid=18326990 }}</ref><ref name="kawash">[https://www.thefreelibrary.com/Complex+quantification+in+Structured+Query+Language+(SQL):+a+tutorial...-a0119901477 Kawash, Jalal (2004) Complex quantification in Structured Query Language (SQL): a tutorial using relational calculus]; ''Journal of Computers in Mathematics and Science Teaching'' {{ISSN\|0731-9258}} Volume 23, Issue 2, 2004 AACE Norfolk, Virginia. Thefreelibrary.com</ref> There is also the {{code\|<row value expression> IS DISTINCT FROM <row value expression>}} infixed comparison operator, which returns TRUE unless both operands are equal or both are NULL. Likewise, IS NOT DISTINCT FROM is defined as {{code\|NOT (<row value expression> IS DISTINCT FROM <row value expression>)}}. [[SQL:1999]] also introduced {{code\|BOOLEAN}} type variables, which according to the standard can also hold Unknown values if it is nullable. In practice, a number of systems (e.g. [[PostgreSQL]]) implement the BOOLEAN Unknown as a BOOLEAN NULL, which the standard says that the NULL BOOLEAN and UNKNOWN "may be used interchangeably to mean exactly the same thing".<ref>{{cite book\|author=C. Date\|title=SQL and Relational Theory: How to Write Accurate SQL Code\|url=https://books.google.com/books?id=Ew06OZtjuJEC&pg=PA83\|year=2011\|publisher=O'Reilly Media, Inc.\|isbn=978-1-4493-1640-2\|page=83}}</ref><ref>ISO/IEC 9075-2:2011 §4.5</ref>

	=== Data manipulation ===		=== Data manipulation ===