SQL Nulls and Two-Valued Logic

Leonid Libkin, Liat Peterfreund

Research output: Chapter in Book/Report/Conference proceedingConference contributionpeer-review

Abstract

The design of SQL is based on a three-valued logic (3VL), rather than the familiar two-valued Boolean logic (2VL). In addition totrue andfalse, 3VL addsunknown to handle nulls. Viewed as indispensable for SQL expressiveness, it is often criticized for unintuitive behavior of queries and for being a source of programmer mistakes. We show that, contrary to the widely held view, SQL could have been designed based on 2VL, without any loss of expressiveness. Similarly to SQL's WHERE clause, which only keeps true tuples, we conflate false and unknown for conditions involving nulls to obtain an equally expressive 2VL-based version of SQL. This applies to the core of the 1999 SQL Standard. Queries written under the 2VL semantics can be efficiently translated into the 3VL SQL and thus executed on any existing RDBMS. We show that 2VL enables additional optimizations. To gauge its applicability, we establish criteria under which 2VL and 3VL semantics coincide, and analyze common benchmarks such as TPC-H and TPC-DS to show that most of their queries are such. For queries that behave differently under 2VL and 3VL, we undertake a user study to show a consistent preference for the 2VL semantics.

Original languageEnglish
Title of host publicationPODS 2023 - Proceedings of the 42nd ACM SIGMOD-SIGACT-SIGAI Symposium on Principles of Database Systems
PublisherAssociation for Computing Machinery
Pages11-20
Number of pages10
ISBN (Electronic)9798400701276
DOIs
StatePublished - 18 Jun 2023
Externally publishedYes
Event42nd ACM SIGMOD-SIGACT-SIGAI Symposium on Principles of Database Systems, PODS 2023 - Seattle, United States
Duration: 18 Jun 202323 Jun 2023

Publication series

NameProceedings of the ACM SIGACT-SIGMOD-SIGART Symposium on Principles of Database Systems

Conference

Conference42nd ACM SIGMOD-SIGACT-SIGAI Symposium on Principles of Database Systems, PODS 2023
Country/TerritoryUnited States
CitySeattle
Period18/06/2323/06/23

Bibliographical note

Publisher Copyright:
© 2023 ACM.

Keywords

  • boolean logic
  • nulls
  • query equivalence
  • query optimization
  • SQL
  • three-valued logic
  • user study

Fingerprint

Dive into the research topics of 'SQL Nulls and Two-Valued Logic'. Together they form a unique fingerprint.

Cite this