Have you ever wondered about the use-case behind SQL’s ANY (also: SOME) and ALL keywords?
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)
See the above queries in action on PostgreSQL in this SQLFiddle.
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) )
Clearly, the 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.
Read on about SQL transformation in this article here.
| Reference: | A Wonderful SQL Feature: Quantified Comparison Predicates (ANY, ALL) from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog. |
