出色的SQL功能：量化比较谓词（任意，全部）

您是否想知道SQL的ANY （ 也： SOME ）和ALL关键字背后的用例？

您可能尚未在野外遇到这些关键字。 但是它们可能非常有用。 但是首先，让我们看看如何在SQL标准中对其进行定义。 最简单的部分：

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

直观地，这样的量化比较谓词可以这样使用：

-- Is any person of age 42?
42 = ANY (SELECT age FROM person)

-- Are all persons younger than 42?
42 > ALL (SELECT age FROM person)

让我们保持有用的。 请注意，您可能已经使用不同的语法编写了上述查询，例如：

-- 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>或大于谓词和<scalar subquery>和一个聚合函数。

IN谓词

您可能使用了<in predicate>就像上面使用ANY <quantified comparison predicate>并不是巧合。 实际上， <in predicate>的指定方式如下：

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

恰好！ SQL不漂亮吗？ 注意， 3)的隐式结果导致NOT IN谓词关于NULL的非常特殊的行为 ，这是很少有开发人员知道的。

现在，它变得很棒

到目前为止，这些<quantified comparison predicate>并没有什么不寻常的。 前面的所有示例都可以用“更惯用的”或“更日常”的SQL进行模拟。

但是，仅当与<row value expression>结合使用时， <quantified comparison predicate>的真正威力才出现，其中行的度数/联合度大于一个：

-- 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)

请参阅此SQLFiddle中有关PostgreSQL的上述查询 。

在这一点上，值得一提的是，实际上很少有数据库支持……

• 行值表达式，或…
• 具有行值表达式的量化比较谓词

即使在SQL-92中指定，看起来22年后大多数数据库仍需要花费时间来实现此功能。

用jOOQ模拟这些谓词

但幸运的是，有jOOQ可以为您模拟这些功能。 即使您没有在项目中使用jOOQ，如果要表达上述谓词，下面的SQL转换步骤也会很有用。 让我们看一下如何在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'
)

那其他谓词呢？

-- 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谓词几乎可以在每个数据库中使用，以模仿我们之前看到的内容。 如果您只需要一次仿真即可，上述示例就足够了。 但是，如果您想更正式地使用<row value expression>和<quantified comparison predicate> ，则最好正确使用SQL转换。

在此处阅读有关SQL转换的内容。

翻译自: https://www.javacodegeeks.com/2014/08/a-wonderful-sql-feature-quantified-comparison-predicates-any-all.html