ALLkeywords? 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 | ANYIntuitively, such a quantified comparison predicate can be used as such:
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 = ANY (SELECT age FROM person) -- Are all persons younger than 42? 42 > ALL (SELECT age FROM person)
In fact, you’ve used the
-- 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 predicate>, or a greater than predicate with a
<scalar subquery>and an aggregate function.
The IN predicateIt’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 IPVPrecisely! Isn’t SQL beautiful? Note, the implicit consequences of
3)lead to a very peculiar behaviour of the
NOT INpredicate with respect to
NULL, which few developers are aware of.
Now, it’s getting awesomeSo 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:
See the above queries in action on PostgreSQL in this SQLFiddle. At this point, it is worth mentioning that few databases actually support…
-- 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)
- row value expressions, or…
- quantified comparison predicates with row value expressions
Emulating these predicates with jOOQBut 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:
What about the other predicate?
-- 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' )
-- 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) )
EXISTSpredicate 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. Read on about SQL transformation in this article here.