結構化查詢語言/量化比較

在某些情況下，應用程式需要比較行或列，而不是與固定值進行比較 - 例如：“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
...