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.Like this:
Like Loading...
I’ unsure about what
(55, 150000.00) > ALL (SELECT age, wage FROM person)
exactly means as there are two common meanings of `>` for tuples (lexicographical and pointwise) and I don’t know which one SQL employs.
However, I’d bet that there’s something wrong with your equivalent expressions:
(55, 150000.00) > (
SELECT age, wage FROM person
ORDER BY 1 DESC, 2 DESC
LIMIT 1
)
With `person = {(1, 200000), (2, 2)}`, the SELECT returns `{1, 200000}`, which would only work assuming lexicographical comparison.
The other expression
EXISTS (
SELECT 1 FROM person
WHERE 55 > age AND 150000.00 > wage
)
replaces a universal quantifier by an existential one without any negations – strange. I’d expect something like
NOT EXISTS (
SELECT 1 FROM person
WHERE 55 <= age AND 150000.00 <= wage
)
However, it can only work assuming pointwise comparison.
Very attentive, you’re right of course. The
EXISTS
predicates are nonesense, of course, as the SQL standard specifies:The correct emulation would be: