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 a (true) with every value of b in order (true, then false, then null), then again with the second value of a (false), and then again with the third value of a (null).

We’re using format and coalesce to produce an easier-to-read result table here. The coalesce function returns the first of its argument, which is not null, with the restriction that all of its arguments must be of the same data type, here text. Here’s the truth table we get:

   a   │   b   │  a=b  │      op       │  result  
═══════╪═══════╪═══════╪═══════════════╪══════════
 true  │ true  │ true  │ true = true   │ is true
 true  │ false │ false │ true = false  │ is false
 true  │ ¤     │ ¤     │ true = null   │ is null
 false │ true  │ false │ false = true  │ is false
 false │ false │ true  │ false = false │ is true
 false │ ¤     │ ¤     │ false = null  │ is null
 ¤     │ true  │ ¤     │ null = true   │ is null
 ¤     │ false │ ¤     │ null = false  │ is null
 ¤     │ ¤     │ ¤     │ null = null   │ is null
(9 rows)

Note: We can think of null as meaning “I don’t know what this is” rather than “no value here.”

Say we have in A, left hand, something hidden and we don’t know what it is, and in B, right hand, something which is also hidden from us. We’re asked if A and B are the same things. We can’t possibly know what those things are, can we?

So in SQL, null = null returns null, which is the proper answer to the question, but not always the one we expect or the one that allows us to write our query and have the expected result set.

Other SQL operators

That’s why we have other SQL operators to work with data that might be null: they are is distinct from and is not distinct from. Those two operators not only have very long names, but they also pretend that null is the same thing as null.

So, if we want to pretend that SQL doesn’t implement three-valued logic, we can use those operators and forget about boolean comparisons returning null.

We can even easily obtain the truth table from a SQL query directly:

PostgreSQL
select a::text as left, b::text as right,
       (a = b)::text as "=",
       (a <> b)::text as "<>",
       (a is distinct from b)::text as "is distinct",
       (a is not distinct from b)::text as "is not distinct from"
  from            (values(true),(false),(null)) t1(a)
       cross join (values(true),(false),(null)) t2(b);

With this complete result this time:

 left  │ right │   =   │  <>   │ is distinct │ is not distinct from 
═══════╪═══════╪═══════╪═══════╪═════════════╪══════════════════════
 true  │ true  │ true  │ false │ false       │ true
 true  │ false │ false │ true  │ true        │ false
 true  │ ¤     │ ¤     │ ¤     │ true        │ false
 false │ true  │ false │ true  │ true        │ false
 false │ false │ true  │ false │ false       │ true
 false │ ¤     │ ¤     │ ¤     │ true        │ false
 ¤     │ true  │ ¤     │ ¤     │ true        │ false
 ¤     │ false │ ¤     │ ¤     │ true        │ false
 ¤     │ ¤     │ ¤     │ ¤     │ false       │ true
(9 rows)

We can see that we don’t have a single null in the last two columns.