SQL Nulls and Two-Valued Logic

Leonid Libkin, Liat Peterfreund

Research output: Chapter in Book/Report/Conference proceedingConference contribution

Abstract / Description of output

The design of SQL is based on a three-valued logic (3VL), rather than the familiar two-valued Boolean logic (2VL). In addition to true and false, 3VL adds unknown 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 trans lated 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 seman tics 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 publicationProceedings of the 42nd ACM SIGMOD-SIGACT-SIGAI Symposium on Principles of Database Systems (PODS ’23)
PublisherACM Association for Computing Machinery
Pages11-20
ISBN (Electronic)9798400701276
DOIs
Publication statusPublished - 18 Jun 2023
Event42nd ACM Symposium on Principles of Database Systems - Seattle , United States
Duration: 18 Jun 202323 Jun 2023
https://2023.sigmod.org/

Publication series

NamePODS '23
PublisherAssociation for Computing Machinery

Symposium

Symposium42nd ACM Symposium on Principles of Database Systems
Abbreviated titlePODS 2023
Country/TerritoryUnited States
CitySeattle
Period18/06/2323/06/23
Internet address

Keywords / Materials (for Non-textual outputs)

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

Fingerprint

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

Cite this