A Wonderful SQL Feature: Quantified Comparison Predicates (ANY, ALL)
Have you ever wondered about the use-case behind SQL’s
You have probably not yet encountered these keywords in the wild. Yet they can be extremely useful. But first, let’s see how they’re defined in the SQL standard. The easy part:
8.7 <quantified comparison predicate> Function Specify a quantified comparison. Format <quantified comparison predicate> ::= <row value constructor> <comp op> <quantifier> <table subquery> <quantifier> ::= <all> | <some> <all> ::= ALL <some> ::= SOME | ANY
Intuitively, such a quantified comparison predicate can be used as such:
-- Is any person of age 42? 42 = ANY (SELECT age FROM person) -- Are all persons younger than 42? 42 > ALL (SELECT age FROM person)
Let’s keep with the useful ones. Observe that you have probably written the above queries with a different syntax, as such:
-- Is any person of age 42? 42 IN (SELECT age FROM person) -- Are all persons younger than 42? 42 > (SELECT MAX(age) FROM person)
In fact, you’ve used the
<in predicate>, or a greater than predicate with a
<scalar subquery> and an aggregate function.
The IN predicate
It’s not a coincidence that you might have used the
<in predicate> just like the above
<quantified comparison predicate> using
ANY. In fact, the
<in predicate> is specified just like that:
8.4 <in predicate> Syntax Rules 2) Let RVC be the <row value constructor> and let IPV be the <in predicate value>. 3) The expression RVC NOT IN IPV is equivalent to NOT ( RVC IN IPV ) 4) The expression RVC IN IPV is equivalent to RVC = ANY IPV
Precisely! Isn’t SQL beautiful? Note, the implicit consequences of
3) lead to a very peculiar behaviour of the
NOT IN predicate with respect to
NULL, which few developers are aware of.
Now, it’s getting awesome
So far, there is nothing out of the ordinary with these
<quantified comparison predicate>. All of the previous examples can be emulated with “more idiomatic”, or let’s say, “more everyday” SQL.
But the true awesomeness of
<quantified comparison predicate> appears only when used in combination with
<row value expression> where rows have a degree / arity of more than one:
-- Is any person called "John" of age 42? (42, 'John') = ANY (SELECT age, first_name FROM person) -- Are all persons younger than 55? -- Or if they're 55, do they all earn less than 150'000.00? (55, 150000.00) > ALL (SELECT age, wage FROM person)
At this point, it is worth mentioning that few databases actually support…
- row value expressions, or…
- quantified comparison predicates with row value expressions
Even if specified in SQL-92, it looks as most databases still take their time to implement this feature 22 years later.
Emulating these predicates with jOOQ
But luckily, there is jOOQ to emulate these features for you. Even if you’re not using jOOQ in your project, the following SQL transformation steps can be useful if you want to express the above predicates. Let’s have a look at how this could be done in MySQL:
-- This predicate (42, 'John') = ANY (SELECT age, first_name FROM person) -- ... is the same as this: EXISTS ( SELECT 1 FROM person WHERE age = 42 AND first_name = 'John' )
What about the other predicate?
-- This predicate (55, 150000.00) > ALL (SELECT age, wage FROM person) -- ... is the same as these: ---------------------------- -- No quantified comparison predicate with -- Row value expressions available (55, 150000.00) > ( SELECT age, wage FROM person ORDER BY 1 DESC, 2 DESC LIMIT 1 ) -- No row value expressions available at all NOT EXISTS ( SELECT 1 FROM person WHERE (55 < age) OR (55 = age AND 150000.00 <= wage) )
EXISTS predicate can be used in pretty much every database to emulate what we’ve seen before. If you just need this for a one-shot emulation, the above examples will be sufficient. If, however, you want to more formally use
<row value expression> and
<quantified comparison predicate>, you better get SQL transformation right.