Row value expressions and the NULL predicate


Row value expressions are something very powerful in SQL. They have been around since the early days of standard SQL, e.g. in SQL 1992, even if not all databases implement them correctly, still today.

As always, the NULL predicate is one that is a bit tricky to understand, also in the context of row value expressions. Have a look at the following expressions:

(A, B) IS NULL
(A, B) IS NOT NULL

The SQL 1992 standard defines that:

8.6  

General Rules

1) Let R be the value of the <row value constructor>.

2) If all the values in R are the null value, then "R IS NULL" is
   true; otherwise, it is false.

3) If none of the values in R are the null value, then "R IS NOT
   NULL" is true; otherwise, it is false.

   Note: For all R, "R IS NOT NULL" has the same result as "NOT
   R IS NULL" if and only if R is of degree 1. Table 12, "<null
   predicate> semantics", specifies this behavior.

Pay some special attention to paragraph 3). Yes, the following two predicates are NOT equivalent!

    (A, B) IS NOT NULL
NOT((A, B) IS     NULL)

This is easy to understand, when factoring out the rules of equivalency:

(A, B) IS NOT NULL                -- equivalent to...
A IS NOT NULL AND B IS NOT NULL   -- equivalent to...
NOT(A IS NULL) AND NOT(B IS NULL) -- equivalent to...
NOT(A IS NULL OR B IS NULL)

-- whereas...
NOT((A, B) IS NULL)               -- equivalent to...
NOT(A IS NULL AND B IS NULL)

The truth table also nicely documents this:

+----------------+-------+-------------+------------+--------------+
|                | R IS  | R IS NOT    | NOT R IS   | NOT R IS NOT |
| Expression     | NULL  | NULL        | NULL       | NULL         |
+----------------+-------+-------------+------------+--------------+
| degree 1: null | true  | false       | false      |  true        |
| degree 1: not  | false | true        | true       |  false       |
| null           |       |             |            |              |
| degree > 1:    | true  | false       | false      |  true        |
| all null       |       |             |            |              |
| degree > 1:    | false | false       | true       |  true        |
| some null      |       |             |            |              |
| degree > 1:    | false | true        | true       |  false       |
| none null      |       |             |            |              |
+----------------+-------+-------------+------------+--------------+

More on row value expressions

jOOQ 3.0 will introduce formal and typesafe support for row value expressions and predicates based thereupon. Stay tuned for more interesting insight and articles about row vlaue expressions and how they’re supported (and/or simulated) in various SQL dialects

Tags: , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,190 other followers

%d bloggers like this: