Search⌘ K
AI Features
Home
Courses
The Art of PostgreSQL

Three-Valued Logic

Explore the concept of three-valued logic in PostgreSQL and its impact on SQL null handling. Understand how nulls differ from typical programming null values, how they affect SQL comparisons, and how operators like 'is distinct from' help manage null values in queries.

We'll cover the following...

Given its relational theory background, SQL comes with a special value that has no counterpart in a common programming language: null. In Python, we have None; in PHP, we have null; in C, we have nil; and about every other programming language has something that looks like a null.

The difference in SQL is that null introduces three-valued logic. This is very different from other languages is that None or Null is used when comparing values. Let’s have a look at the SQL null truth table:

PostgreSQL
select a::text, b::text,
       (a=b)::text as "a=b",
       format('%s = %s',
              coalesce(a::text, 'null'),
              coalesce(b::text, 'null')) as op,
       format('is %s',
              coalesce((a=b)::text, 'null')) as result
  from (values(true), (false), (null)) v1(a)
       cross join
       (values(true), (false), (null)) v2(b);

As we can see, the cross join is very useful for producing a truth table. It implements a Cartesian product over our columns, here listing the first value of ...