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
Like this:
Like Loading...
Published by lukaseder
I made jOOQ
View all posts by lukaseder
