如何使用 SQL AND、OR、IN 和 NOT 過濾返回的數據

本文介紹如何用 AND 和 OR 操作符組合成 WHERE 子句以建立功能更強、更高級的搜索條件。我們還介紹瞭如何使用 NOT 和 IN 操作符。一、組合 WHERE 子句在如何使用 SQL WHERE 過濾返回的數據中介紹的所有 WHERE 子句在過濾數據時使用的都是單一的條件。為了進行 ...

一、組合 WHERE 子句
二、IN 操作符
三、NOT 操作符
四、小結

本文介紹如何用 AND 和 OR 操作符組合成 WHERE 子句以建立功能更強、更高級的搜索條件。我們還介紹瞭如何使用 NOT 和 IN 操作符。

一、組合 WHERE 子句

在如何使用 SQL WHERE 過濾返回的數據中介紹的所有 WHERE 子句在過濾數據時使用的都是單一的條件。

為了進行更強的過濾控制，SQL 允許給出多個 WHERE 子句。這些子句有兩種使用方式，即以 AND 子句或 OR 子句的方式使用。

操作符（operator）

用來聯結或改變 WHERE 子句中的子句的關鍵字，也稱為邏輯操作符（logical operator）。

1.1 AND 操作符

要通過不止一個列進行過濾，可以使用 AND 操作符給 WHERE 子句附加條件。下麵的代碼給出了一個例子：

SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

此 SQL 語句檢索由供應商 DLL01 製造且價格小於等於 4 元的所有產品的名稱和價格。

這條 SELECT 語句中的 WHERE 子句包含兩個條件，用 AND 關鍵字聯結在一起。AND 指示 DBMS 只返回滿足所有給定條件的行。

如果某個產品由供應商 DLL01 製造，但價格高於 4 元，則不檢索它。

類似地，如果產品價格小於 4 元，但不是由指定供應商製造的也不被檢索。這條 SQL 語句產生的輸出如下：

prod_id     prod_price     prod_name
-------     ----------     --------------------
BNBG02      3.4900         Bird bean bag toy
BNBG01      3.4900         Fish bean bag toy
BNBG03      3.4900         Rabbit bean bag toy

AND

用在 WHERE 子句中的關鍵字，用來指示檢索滿足所有給定條件的行。

這個例子只包含一個 AND 子句，因此只有兩個過濾條件。可以增加多個過濾條件，每個條件間都要使用 AND 關鍵字。

說明：沒有 ORDER BY 子句

為了節省空間，也為了減少你的輸入，我在很多例子里省略了 ORDER BY 子句。

因此，你的輸出完全有可能與本文中的輸出不一致。雖然返回行的數量總是對的，但它們的順序可能不同。

當然，如果你願意也可以加上一個 ORDER BY 子句，它應該放在 WHERE 子句之後。

1.2 OR 操作符

OR 操作符與 AND 操作符正好相反，它指示 DBMS 檢索匹配任一條件的行。

事實上，許多 DBMS 在 OR WHERE 子句的第一個條件得到滿足的情況下，就不再計算第二個條件了（在第一個條件滿足時，不管第二個條件是否滿足，相應的行都將被檢索出來）。

請看如下的 SELECT 語句：

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

此 SQL 語句檢索由任一個指定供應商製造的所有產品的產品名和價格。

OR 操作符告訴 DBMS 匹配任一條件而不是同時匹配兩個條件。

如果這裡使用的是 AND 操作符，則沒有數據返回（因為會創建沒有匹配行的 WHERE 子句）。

這條 SQL 語句產生的輸出如下：

prod_name               prod_price
-------------------     ----------
Fish bean bag toy       3.4900
Bird bean bag toy       3.4900
Rabbit bean bag toy     3.4900
8 inch teddy bear       5.9900
12 inch teddy bear      8.9900
18 inch teddy bear      11.9900
Raggedy Ann             4.9900

OR

WHERE 子句中使用的關鍵字，用來表示檢索匹配任一給定條件的行。

1.3 求值順序

WHERE 子句可以包含任意數目的 AND 和 OR 操作符。允許兩者結合以進行複雜、高級的過濾。但是，組合 AND 和 OR 會帶來了一個有趣的問題。為了說明這個問題，來看一個例子。

假如需要列出價格為 10 元及以上，且由 DLL01 或 BRS01 製造的所有產品。下麵的 SELECT 語句使用組合的 AND 和 OR 操作符建立了一個 WHERE 子句：

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'
      AND prod_price >= 10;

輸出：

prod_name               prod_price
-------------------     ----------
Fish bean bag toy       3.4900
Bird bean bag toy       3.4900
Rabbit bean bag toy     3.4900
18 inch teddy bear      11.9900
Raggedy Ann             4.9900

請看上面的結果。返回的行中有 4 行價格小於 10 美元，顯然，返回的行未按預期的進行過濾。

為什麼會這樣呢？原因在於求值的順序。

SQL（像多數語言一樣）在處理 OR 操作符前，優先處理 AND 操作符。

當 SQL 看到上述 WHERE 子句時，它理解為：由供應商 BRS01 製造的價格為 10 元以上的所有產品，以及由供應商 DLL01 製造的所有產品，而不管其價格如何。

換句話說，由於 AND 在求值過程中優先順序更高，操作符被錯誤地組合了。

此問題的解決方法是使用圓括弧對操作符進行明確分組。請看下麵的 SELECT 語句及輸出：

SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
      AND prod_price >= 10;

輸出：

prod_name               prod_price
-------------------     ----------
18 inch teddy bear      11.9900

這條 SELECT 語句與前一條的唯一差別是，將前兩個條件用圓括弧括了起來。

因為圓括弧具有比 AND 或 OR 操作符更高的優先順序，所以 DBMS 首先過濾圓括弧內的 OR 條件。

這時，SQL 語句變成了選擇由供應商 DLL01 或 BRS01 製造的且價格在 10 元及以上的所有產品，這正是我們希望的結果。

提示：在 WHERE 子句中使用圓括弧

任何時候使用具有 AND 和 OR 操作符的 WHERE 子句，都應該使用圓括弧明確地分組操作符。

不要過分依賴預設求值順序，即使它確實如你希望的那樣。使用圓括弧沒有什麼壞處，它能消除歧義。

二、IN 操作符

IN 操作符用來指定條件範圍，範圍中的每個條件都可以進行匹配。IN 取一組由逗號分隔、括在圓括弧中的合法值。下麵的例子說明瞭這個操作符。

SELECT prod_name, prod_price
FROM Products
WHERE vend_id  IN ('DLL01','BRS01')
ORDER BY prod_name;

輸出：

prod_name               prod_price
-------------------     ----------
12 inch teddy bear      8.9900
18 inch teddy bear      11.9900
8 inch teddy bear       5.9900
Bird bean bag toy       3.4900
Fish bean bag toy       3.4900
Rabbit bean bag toy     3.4900
Raggedy Ann             4.9900

此 SELECT 語句檢索由供應商 DLL01 和 BRS01 製造的所有產品。IN 操作符後跟由逗號分隔的合法值，這些值必須括在圓括弧中。

你可能會猜測 IN 操作符完成了與 OR 相同的功能，恭喜你猜對了！下麵的 SQL 語句完成與上面的例子相同的工作。

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'
ORDER BY prod_name;

輸出：

prod_name               prod_price
-------------------     ----------
12 inch teddy bear      8.9900
18 inch teddy bear      11.9900
8 inch teddy bear       5.9900
Bird bean bag toy       3.4900
Fish bean bag toy       3.4900
Rabbit bean bag toy     3.4900
Raggedy Ann             4.9900

為什麼要使用 IN 操作符？其優點如下。

在有很多合法選項時，IN 操作符的語法更清楚，更直觀。
在與其他 AND 和 OR 操作符組合使用 IN 時，求值順序更容易管理。
IN 操作符一般比一組 OR 操作符執行得更快（在上面這個合法選項很少的例子中，你看不出性能差異）。
IN 的最大優點是可以包含其他 SELECT 語句，能夠更動態地建立 WHERE 子句。SQL 如何使用子查詢對此進行詳細介紹。

IN

WHERE 子句中用來指定要匹配值的清單的關鍵字，功能與 OR 相當。

三、NOT 操作符

WHERE 子句中的 NOT 操作符有且只有一個功能，那就是否定其後所跟的任何條件。因為 NOT 從不單獨使用（它總是與其他操作符一起使用），所以它的語法與其他操作符有所不同。

NOT 關鍵字可以用在要過濾的列前，而不僅是在其後。

NOT

WHERE 子句中用來否定其後條件的關鍵字。

下麵的例子說明 NOT 的用法。為了列出除 DLL01 之外的所有供應商製造的產品，可編寫如下的代碼。

SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

輸出：

prod_name
------------------
12 inch teddy bear
18 inch teddy bear
8 inch teddy bear
King doll
Queen doll

這裡的 NOT 否定跟在其後的條件，因此，DBMS 不是匹配 vend_id 為 DLL01，而是匹配非 DLL01 之外的所有東西。

上面的例子也可以使用 <> 操作符來完成，如下所示。

SELECT prod_name
FROM Products
WHERE vend_id  <> 'DLL01'
ORDER BY prod_name;

輸出：

prod_name
------------------
12 inch teddy bear
18 inch teddy bear
8 inch teddy bear
King doll
Queen doll

為什麼使用 NOT？對於這裡的這種簡單的 WHERE 子句，使用 NOT 確實沒有什麼優勢。但在更複雜的子句中，NOT 是非常有用的。

例如，在與 IN 操作符聯合使用時，NOT 可以非常簡單地找出與條件列表不匹配的行。

說明：MariaDB 中的 NOT

MariaDB 支持使用 NOT 否定 IN、BETWEEN 和 EXISTS 子句。大多數 DBMS 允許使用 NOT 否定任何條件。

四、小結

本文介紹瞭如何用 AND 和 OR 操作符組合成 WHERE 子句以建立功能更強、更高級的搜索條件。還介紹瞭如何明確地管理求值順序，以及如何使用 NOT 和 IN 操作符。

原文鏈接：https://www.developerastrid.com/sql/sql-and-or-in-not/

（完）