结构化查询语言/量化比较

在某些情况下，应用程序需要比较行或列，而不是与固定值进行比较 - 例如：“WHERE status = 5” - 而是与在运行时计算的查询结果进行比较。此类动态查询的第一个例子是子查询，它恰好返回一个值：“... WHERE version = (SELECT MAX(version) ...)”。此外，有时需要与包含多个值的集合进行比较：“... WHERE version <comparison> (SELECT version FROM t1 WHERE status > 2 ...)”。

为了实现这一点，SQL 提供了一些特殊的比较方法，用于在要查询的表和子查询的结果之间进行比较：IN、ALL、ANY/SOME 和 EXISTS。它们属于所谓的谓词组。

• IN 谓词检索与子查询结果值相对应的行。
• ALL 谓词（与 <、<=、=、>=、> 或 <> 结合使用）检索与子查询的所有值相对应的行（布尔 AND 操作）。
• ANY 谓词（与 <、<=、=、>=、> 或 <> 结合使用）检索与子查询的任何值相对应的行（布尔 OR 操作）。关键字 SOME 可用作 ANY 的同义词，因此可以将两者互换使用。
• EXISTS 谓词检索行，如果子查询检索到一行或多行。

IN

IN 谓词 - 如上一章所述 - 接受一组值或行。

SELECT * 
FROM   person 
WHERE  id IN 
  (SELECT person_id FROM contact); -- Subquery with potentially a lot of rows.

子查询选择很多值。因此，无法使用诸如“=”或“>”之类的运算符。它们只会比较单个值。但 IN 谓词处理这种情况，并将person.id与contact.person_id的每个值进行比较，而不管contact.person_id值的个数。这些比较在布尔 OR 操作的意义上是相互关联的。

可以通过添加关键字 NOT 来否定 IN 谓词。

...
WHERE  id NOT IN
...

ALL

ALL 谓词将每行与子查询的每个值进行比较，其意义与布尔 AND相同。与 IN 谓词不同，它需要一个额外的运算符，该运算符可以是：<、<=、=、>=、> 或 <>。

SELECT *
FROM   person 
WHERE  weight > ALL  
  (SELECT weight FROM person where lastname = 'de Winter');

常见提示：如果子查询中没有 NULL 特殊标记，则可以将 ALL 谓词替换为等效的（更直观的）操作

<op> ALL	替换
< ALL	< (SELECT MIN() ...)
<= ALL	<= (SELECT MIN() ...)
= ALL	'=' 或 'IN'，如果子选择检索到 1 个值。
	否则：单个值不可能同时等于不同的值。（x = a AND x = b）在所有情况下都计算为“false”。
>= ALL	>= (SELECT MAX() ...)
> ALL	> (SELECT MAX() ...)
<> ALL	'<>' 或 'NOT IN'，如果子选择检索到 1 个值。
	否则：'NOT IN'。（x <> a AND x <> b）。

MySQL 提示：由于查询重写问题，应禁用 ONLY_FULL_GROUP_BY 模式，例如通过以下命令：set sql_mode='ANSI'。

ANY/SOME

关键字 ANY 和 SOME 是同义词，它们的含义相同。在本维基教科书中，我们更喜欢使用 ANY。

ANY 谓词将每行与子查询的每个值进行比较，其意义与布尔 OR相同。与 IN 谓词不同，它需要一个额外的运算符，该运算符可以是：<、<=、=、>=、> 或 <>。

SELECT *
FROM   person 
WHERE  weight > ANY  
  (SELECT weight FROM person where lastname = 'de Winter');

常见提示：如果子查询中没有 NULL 特殊标记，则可以将 ANY 谓词替换为等效的（更直观的）操作

<op> ANY	替换
< ANY	< (SELECT MAX() ...)
<= ANY	<= (SELECT MAX() ...)
= ANY	'=' 或 'IN'，如果子选择检索到 1 个值。
	否则：'IN'。（x = a OR x = b）。
>= ANY	>= (SELECT MIN() ...)
> ANY	> (SELECT MIN() ...)
<> ANY	'<>' 或 'NOT IN'，如果子选择检索到 1 个值。
	否则：在 OR 连接下，单个值始终不同于两个或多个不同的值。（x <> a OR x <> b）在所有情况下都计算为“true”。

MySQL 提示：由于查询重写问题，应禁用 ONLY_FULL_GROUP_BY 模式，例如通过以下命令：set sql_mode='ANSI'。

EXISTS

EXISTS 谓词检索行，如果子查询检索到一行或多行。有意义的示例通常使用关联子查询。

SELECT *
FROM   contact c1
WHERE EXISTS
  (SELECT  *
   FROM    contact c2
   WHERE   c2.person_id = c1.person_id  -- correlation criterion between query and subquery
   AND     c2.contact_type = 'icq');

此示例检索所有具有 ICQ 联系人的人的联系信息。

可以通过添加关键字 NOT 来否定 EXISTS 谓词。

...
WHERE NOT EXISTS
...