A Wonderful SQL Feature: Quantified Comparison Predicates (ANY, ALL)

Posted on by lukaseder
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.

Published by lukaseder

I made jOOQ

2 thoughts on “A Wonderful SQL Feature: Quantified Comparison Predicates (ANY, ALL)”

  1. 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.

    Reply

    1. Very attentive, you’re right of course. The EXISTS predicates are nonesense, of course, as the SQL standard specifies:

      7) Let Rx and Ry be the two <row value constructor>s of the <com-
   parison predicate> and let RXi and RYi be the i-th <row value
   constructor element>s of Rx and Ry, respectively. "Rx <comp op>
   Ry" is true, false, or unknown as follows:

   a) "x = Ry" is true if and only if RXi = RYi for all i.

   b) "x <> Ry" is true if and only if RXi <> RYi for some i.

   c) "x < Ry" is true if and only if RXi = RYi for all i < n and
      RXn < RYn for some n.

   d) "x > Ry" is true if and only if RXi = RYi for all i < n and
      RXn > RYn for some n.

      The correct emulation would be:

      NOT EXISTS (
  SELECT 1 FROM person
  WHERE (55 < age)
  OR    (55 = age AND 150000.00 <= wage)
)
      Reply

Leave a Reply