结构化查询语言(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 值时，可以使用以下替代方法：
| 操作符 + ALL | 替代操作 |
|---|---|
< ALL |
< (SELECT MIN() ...) |
<= ALL |
<= (SELECT MIN() ...) |
= ALL |
'=' 或 IN（当子查询返回 1 个值） |
>= ALL |
>= (SELECT MAX() ...) |
> ALL |
> (SELECT MAX() ...) |
<> ALL |
NOT IN（当子查询返回 1 个值） |
ANY/SOME 谓词
ANY 和 SOME 是同义词，表示任意值匹配。操作符包括 <, <=, =, >=, >, <>，这些比较通过布尔 OR 操作连接。
示例
检索 person 表中 weight 大于子查询返回的任意 weight 值的行：
SELECT *
FROM person
WHERE weight > ANY
(SELECT weight FROM person WHERE lastname = 'de Winter');
替代方式
当子查询不包含 NULL 值时，可以使用以下替代方法：
| 操作符 + ANY | 替代操作 |
|---|---|
< ANY |
< (SELECT MAX() ...) |
<= ANY |
<= (SELECT MAX() ...) |
= ANY |
'=' 或 IN（当子查询返回 1 个值） |
>= ANY |
>= (SELECT MIN() ...) |
> ANY |
> (SELECT MIN() ...) |
<> ANY |
'NOT IN'（当子查询返回 1 个值） |
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 ...
总结
- IN 用于匹配一组值。
- ALL 强调匹配子查询中的所有值（布尔 AND）。
- ANY/SOME 强调匹配任意值（布尔 OR）。
- EXISTS 检查子查询是否返回结果，常用于相关子查询。
每种谓词都有其适用场景，可根据需求灵活使用。