Search⌘ K
Home
Courses
The Art of PostgreSQL

Three-Valued Logic

Learn null in SQL through a few examples using truth tables.

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 ...