结构化查询语言(Structured Query Language)

在某些用例中，应用程序需要与运行时动态计算的查询结果进行比较，而不是与固定值比较。例如，WHERE status = 5 是固定值比较，而 WHERE version = (SELECT MAX(version) ...) 是动态查询的比较。此外，有时需要与包含多个值的集合进行比较，例如：WHERE version <comparison> (SELECT version FROM t1 WHERE status > 2 ...)。

为此，SQL 提供了一些特殊的比较方法，用于在查询的表与子查询结果之间进行比较，包括 IN、ALL、ANY/SOME 和 EXISTS。这些方法被称为谓词（Predicates）。

谓词简介

• IN：检索与子查询结果中的值匹配的行。
• ALL：结合操作符（如 <, <=, =, >=, >, <>），检索与子查询中所有值都匹配的行（布尔 AND 操作）。
• ANY/SOME：结合操作符，检索与子查询中任意值匹配的行（布尔 OR 操作）。SOME 是 ANY 的同义词。
• EXISTS：如果子查询返回一行或多行，则检索满足条件的行。

IN 谓词

IN 用于匹配子查询结果中的值。

示例

检索 person 表中 id 值在 contact 表的 person_id 列中的所有行：

SELECT * 
FROM   person 
WHERE  id IN 
  (SELECT person_id FROM contact);

• 子查询返回多个值，因此无法使用 = 或 > 等操作符。
• IN 将 person.id 与 contact.person_id 的所有值逐一比较，这些比较通过布尔 OR 操作连接。

可以通过添加 NOT 对 IN 谓词进行取反：

WHERE id NOT IN ...

ALL 谓词

ALL 将子查询中的每个值与表中的行进行比较，操作符包括 <, <=, =, >=, >, <>，这些比较通过布尔 AND 操作连接。

示例

检索 person 表中 weight 大于子查询返回的所有 weight 值的行：

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

替代方式

当子查询不包含 NULL 值时，可以使用以下替代方法：

ANY/SOME 谓词

ANY 和 SOME 是同义词，表示任意值匹配。操作符包括 <, <=, =, >=, >, <>，这些比较通过布尔 OR 操作连接。

示例

检索 person 表中 weight 大于子查询返回的任意 weight 值的行：

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

替代方式

当子查询不包含 NULL 值时，可以使用以下替代方法：

EXISTS 谓词

EXISTS 检查子查询是否返回至少一行。如果返回一行或多行，则检索满足条件的行。通常结合相关子查询（correlated subquery）使用。

示例

检索所有拥有 ICQ 联系方式的人的联系方式：

SELECT *
FROM   contact c1
WHERE EXISTS
  (SELECT  *
   FROM    contact c2
   WHERE   c2.person_id = c1.person_id  -- 相关条件
   AND     c2.contact_type = 'icq');

可以通过添加 NOT 对 EXISTS 谓词进行取反：

WHERE NOT EXISTS ...

总结

1. IN 用于匹配一组值。
2. ALL 强调匹配子查询中的所有值（布尔 AND）。
3. ANY/SOME 强调匹配任意值（布尔 OR）。
4. EXISTS 检查子查询是否返回结果，常用于相关子查询。

每种谓词都有其适用场景，可根据需求灵活使用。