Handling SQL Nulls with Two-Valued Logic

by   Leonid Libkin, et al.

The design of SQL is based on a three-valued logic (3VL), rather than the familiar Boolean logic with truth values true and false, to accommodate the additional truth value unknown for handling nulls. It is viewed as indispensable for SQL expressiveness but is at the same time much criticized for leading to unintuitive behavior of queries and thus being a source of programmer mistakes. We show that, contrary to the widely held view, SQL could have been designed based on the standard Boolean logic, without any loss of expressiveness and without giving up nulls. The approach itself follows SQL's evaluation which only retains tuples for which conditions in the WHERE clause evaluate to true. We show that conflating unknown, resulting from nulls, with false leads to an equally expressive version of SQL that does not use the third truth value. Queries written under the two-valued semantics can be efficiently translated into the standard SQL and thus executed on any existing RDBMS. These results cover the core of the SQL 1999 Standard, including SELECT-FROM-WHERE-GROUP BY-HAVING queries extended with subqueries and IN/EXISTS/ANY/ALL conditions, and recursive queries. We provide two extensions of this result showing that no other way of converting 3VL into Boolean logic, nor any other many-valued logic for treating nulls could have possibly led to a more expressive language. These results not only present small modifications of SQL that eliminate the source of many programmer errors without the need to reimplement database internals, but they also strongly suggest that new query languages for various data models do not have to follow the much criticized SQL's three-valued approach.


page 1

page 2

page 3

page 4


No More Nulls!

Since the inception of SQL, nulls have frustrated database users and bui...

Comprehending nulls

The Nested Relational Calculus (NRC) has been an influential high-level ...

Demonstration of LogicLib: An Expressive Multi-Language Interface over Scalable Datalog System

With the ever-increasing volume of data, there is an urgent need to prov...

Four-Valued Semantics for Deductive Databases

In this paper, we introduce a novel approach to deductive databases mean...

Efficient Handling of SPARQL OPTIONAL for OBDA (Extended Version)

OPTIONAL is a key feature in SPARQL for dealing with missing information...

QueryVis: Logic-based diagrams help users understand complicated SQL queries faster

Understanding the meaning of existing SQL queries is critical for code m...

Applying Constraint Logic Programming to SQL Semantic Analysis

This paper proposes the use of Constraint Logic Programming (CLP) to mod...

Please sign up or login with your details

Forgot password? Click here to reset