SQL 謂詞簡介

一、什麼是謂詞

二、LIKE 謂詞——字元串的部分一緻查詢

2.1 前方一緻查詢

2.2 中間一緻查詢

2.3 後方一緻查詢

三、BETWEEN 謂詞——範圍查詢

四、IS NULL、IS NOT NULL——判斷是否為 NULL

五、IN 謂詞——OR 的簡便用法

六、使用子查詢作為 IN 謂詞的參數

6.1 IN 和子查詢

6.2 NOT IN 和子查詢

七、EXISTS 謂詞

7.1 EXISTS 謂詞的使用方法

7.1.1 EXISTS 的參數

7.1.2 子查詢中的 SELECT *

7.1.3 使用 NOT EXISTS 替換 NOT IN

請參閱

學習重點謂詞就是傳回值為真值的函數。掌握 <code>LIKE</code> 的三種使用方法（前方一緻、中間一緻、後方一緻）。需要注意 <code>BETWEEN</code> 包含三個參數。想要取得 <code>NULL</code> 資料時必須使用 <code>IS NULL</code>。可以将子查詢作為 <code>IN</code> 和 <code>EXISTS</code> 的參數。

本文将會和大家一起學習 SQL 的抽出條件中不可或缺的工具——謂詞（predicate）。雖然之前我們沒有提及謂詞這個概念，但其實大家已經使用過了。例如，<code>=</code>、<code><</code>、<code>></code>、<code><></code> 等比較運算符，其正式的名稱就是比較謂詞。

KEYWORD 謂詞

通俗來講謂詞就是各種各樣的函數中介紹的函數中的一種，是需要滿足特定條件的函數，該條件就是傳回值是真值。對通常的函數來說，傳回值有可能是數字、字元串或者日期等，但是謂詞的傳回值全都是真值（<code>TRUE</code>/<code>FALSE</code>/<code>UNKNOWN</code>）。這也是謂詞和函數的最大差別。

本文将會介紹以下謂詞。

<code>BETWEEN</code>

<code>EXISTS</code>

截至目前，我們使用字元串作為查詢條件的例子中使用的都是 <code>=</code>。這裡的 <code>=</code> 隻有在字元串完全一緻時才為真。與之相反，<code>LIKE</code> 謂詞更加模糊一些，當需要進行字元串的部分一緻查詢時需要使用該謂詞。

<code>LIKE</code> 謂詞部分一緻查詢

部分一緻大體可以分為前方一緻、中間一緻和後方一緻三種類型。接下來就讓我們來看一看具體示例吧。

首先我們來建立一張表 1 那樣的隻有 1 列的表。

表 6-1　<code>SampleLike</code> 表

<code>strcol</code>（字元串）|

---|---

abcddd |

dddabc |

abdddc |

abcdd |

ddabc |

abddc |

建立上表以及向其中插入資料的 SQL 語句請參考代碼清單 21。

代碼清單 21　建立 <code>SampleLike</code> 表

SQL Server PostgreSQL

特定的 SQL 不同的 DBMS 事務處理的文法也不盡相同。代碼清單 21 中的 DML 語句在 MySQL 中執行時，需要将 ① 部分更改為“<code>START TRANSACTION;</code>”，在 Oracle 和 DB2 中執行時，無需用到 ① 的部分（請删除）。詳細内容請大家參考事務中的“建立事務”。

想要從該表中讀取出包含字元串“<code>ddd</code>”的記錄時，可能會得到前方一緻、中間一緻和後方一緻等不同的結果。

前方一緻：選取出“<code>dddabc</code>”

所謂前方一緻，就是選取出作為查詢條件的字元串（這裡是“<code>ddd</code>”）與查詢對象字元串起始部分相同的記錄的查詢方法。

中間一緻：選取出“<code>abcddd</code>”“<code>dddabc</code>”“<code>abdddc</code>”

所謂中間一緻，就是選取出查詢對象字元串中含有作為查詢條件的字元串（這裡是“<code>ddd</code>”）的記錄的查詢方法。無論該字元串出現在對象字元串的最後還是中間都沒有關系。

後方一緻：選取出“<code>abcddd</code>”

後方一緻與前方一緻相反，也就是選取出作為查詢條件的字元串（這裡是“<code>ddd</code>”）與查詢對象字元串的末尾部分相同的記錄的查詢方法。

前方一緻中間一緻後方一緻

從本例中我們可以看出，查詢條件最寬松，也就是能夠取得最多記錄的是中間一緻。這是因為它同時包含前方一緻和後方一緻的查詢結果。

像這樣不使用“<code>=</code>”來指定條件字元串，而以字元串中是否包含該條件（本例中是“包含 <code>ddd</code>”）的規則為基礎的查詢稱為模式比對，其中的模式也就是前面提到的“規則”。

模式比對模式

下面讓我們來實際操作一下，對 <code>SampleLike</code> 表進行前方一緻查詢（代碼清單 22）。

代碼清單 22　使用 <code>LIKE</code> 進行前方一緻查詢

執行結果

其中的 <code>%</code> 是代表“0 字元以上的任意字元串”的特殊符号，本例中代表“以 <code>ddd</code> 開頭的所有字元串”。

<code>%</code>

這樣我們就可以使用 <code>LIKE</code> 和模式比對來進行查詢了。

接下來讓我們看一個中間一緻查詢的例子，查詢出包含字元串“<code>ddd</code>”的記錄（代碼清單 23）。

代碼清單 23　使用 <code>LIKE</code> 進行中間一緻查詢

在字元串的起始和結束位置加上 <code>%</code>，就能取出“包含 <code>ddd</code> 的字元串”了。

最後我們來看一下後方一緻查詢，選取出以字元串“<code>ddd</code>”結尾的記錄（代碼清單 24）。

代碼清單 24　使用 <code>LIKE</code> 進行後方一緻查詢

大家可以看到上述結果與前方一緻正好相反。

此外，我們還可以使用 <code>_</code>（下劃線）來代替 <code>%</code>，與 <code>%</code> 不同的是，它代表了“任意 1 個字元”。下面就讓我們來嘗試一下吧。

<code>_</code>

使用代碼清單 25 選取出 <code>strcol</code> 列的值為“<code>abc</code> + 任意 2 個字元”的記錄。

代碼清單 25　使用 <code>LIKE</code> 和 <code>_</code>（下劃線）進行前方一緻查詢

“<code>abcddd</code>”也是以“<code>abc</code>”開頭的字元串，但是其中“<code>ddd</code>”是 3 個字元，是以不滿足 <code>__</code> 所指定的 2 個字元的條件，是以該字元串并不在查詢結果之中。相反，代碼清單 26 中的 SQL 語句就隻能取出“<code>abcddd</code>”這個結果。

代碼清單 26　查詢“<code>abc</code> + 任意 3 個字元”的字元串

使用 <code>BETWEEN</code> 可以進行範圍查詢。該謂詞與其他謂詞或者函數的不同之處在于它使用了 3 個參數。例如，從 <code>product</code>（商品）表中讀取出銷售單價（<code>sale_price</code>）為 100 日元到 1000 日元之間的商品時，可以使用代碼清單 27 中的 SQL 語句。

<code>BETWEEN</code> 謂詞範圍查詢

代碼清單 27　選取銷售單價為 100 ～ 1000 日元的商品

<code>BETWEEN</code> 的特點就是結果中會包含 100 和 1000 這兩個臨界值。如果不想讓結果中包含臨界值，那就必須使用 <code><</code> 和 <code>></code>（代碼清單 28）。

<code><</code> <code>></code>

代碼清單 28　選取出銷售單價為 101 ～ 999 日元的商品

執行結果中不再包含 1000 日元和 100 日元的記錄。

為了選取出某些值為 <code>NULL</code> 的列的資料，不能使用 <code>=</code>，而隻能使用特定的謂詞 <code>IS NULL</code>（代碼清單 29）。

<code>IS NULL</code> 謂詞

代碼清單 29　選取出進貨單價（<code>purchase_price</code>）為 <code>NULL</code> 的商品

與此相反，想要選取 <code>NULL</code> 以外的資料時，需要使用 <code>IS NOT NULL</code>（代碼清單 30）。

<code>IS NOT NULL</code> 謂詞

代碼清單 30　選取進貨單價（<code>purchase_price</code>）不為 <code>NULL</code> 的商品

接下來讓我們思考一下如何選取出進貨單價（<code>purchase_price</code>）為 320 日元、500 日元、5000 日元的商品。這裡使用之前學過的 <code>OR</code> 的 SQL 語句，請參考代碼清單 31。

代碼清單 31　通過 <code>OR</code> 指定多個進貨單價進行查詢

雖然上述方法沒有問題，但還是存在一點不足之處，那就是随着希望選取的對象越來越多，SQL 語句也會越來越長，閱讀起來也會越來越困難。這時，我們就可以使用代碼清單 32 中的 <code>IN</code> 謂詞“<code>IN( 值,……)</code>”來替換上述 SQL 語句。

<code>IN</code> 謂詞

代碼清單 32　通過 <code>IN</code> 來指定多個進貨單價進行查詢

反之，希望選取出“進貨單價不是 320 日元、500 日元、5000 日元”的商品時，可以使用否定形式 <code>NOT IN</code> 來實作（代碼清單 33）。

<code>NOT IN</code> 謂詞

代碼清單 33　使用 <code>NOT IN</code> 進行查詢時指定多個排除的進貨單價進行查詢

但需要注意的是，在使用 <code>IN</code> 和 <code>NOT IN</code> 時是無法選取出 <code>NULL</code> 資料的。實際結果也是如此，上述兩組結果中都不包含進貨單價為 <code>NULL</code> 的叉子和原子筆。<code>NULL</code> 終究還是需要使用 <code>IS NULL</code> 和 <code>IS NOT NULL</code> 來進行判斷。

<code>IN</code> 謂詞（<code>NOT IN</code> 謂詞）具有其他謂詞所沒有的用法，那就是可以使用子查詢作為其參數。我們已經在子查詢中學習過了，子查詢就是 SQL 内部生成的表，是以也可以說“能夠将表作為 <code>IN</code> 的參數”。同理，我們還可以說“能夠将視圖作為 <code>IN</code> 的參數”。

為了掌握詳細的使用方法，讓我們再添加一張新表。之前我們使用的全都是顯示商品庫存清單的 <code>Product</code>（商品）表，但現實中這些商品可能隻在個别的商店中進行銷售。下面我們來建立表 2 <code>ShopProduct</code>（商店商品），顯示出哪些商店銷售哪些商品。

表 2　<code>ShopProduct</code>（商店商品）表

（商店）

（商店名稱）

<code>product_id</code>

（商品編号）

<code>quantity</code>

（數量）

000A

東京

0001

0002

0003

000B

名古屋

120

0004

0006

0007

000C

大阪

000D

福岡

100

商店和商品組合成為一條記錄。例如，該表顯示出東京店銷售的商品有 0001（T 恤衫）、0002（打孔器）、0003（運動 T 恤）三種。

建立該表的 SQL 語句請參考代碼清單 34。

代碼清單 34　建立 <code>ShopProduct</code>（商店商品）表的 <code>CREATE TABLE</code> 語句

該 <code>CREATE TABLE</code> 語句的特點是指定了 2 列作為主鍵（primary key）。這樣做當然還是為了區分表中每一行資料，由于單獨使用商店編号（<code>shop_id</code>）或者商品編号（<code>product_id</code>）不能滿足要求，是以需要對商店和商品進行組合。

實際上如果隻使用商店編号進行區分，那麼指定“<code>000A</code>”作為條件能夠查詢出 3 行資料。而單獨使用商品編号進行區分的話，“<code>0001</code>”也會查詢出 2 行資料，都無法恰當區分每行資料。

下面讓我們來看一下向 <code>ShopProduct</code> 表中插入資料的 <code>INSERT</code> 語句（代碼清單 35）。

代碼清單 35　向 <code>ShopProduct</code> 表中插入資料的 <code>INSERT</code> 語句

不同的 DBMS 事務處理的文法也不盡相同。代碼清單 35 在 MySQL 中執行時，需要将 ① 部分更改為“<code>START TRANSACTION;</code>”，在 Oracle 和 DB2 中執行時，無需用到 ① 的部分（請删除）。

這樣我們就完成了全部準備工作，下面就讓我們來看一看在 <code>IN</code> 謂詞中使用子查詢的 SQL 的寫法吧。

首先讀取出“大阪店（000C）在售商品（<code>product_id</code>）的銷售單價（<code>sale_price</code>）”。

<code>ShopProduct</code>（商店商品）表中大阪店的在售商品很容易就能找出，有如下 4 種。

運動 T 恤（商品編号：0003）

菜刀（商品編号：0004）

叉子（商品編号：0006）

擦菜闆（商品編号：0007）

結果自然也應該是下面這樣。

得到上述結果時，我們應該已經完成了如下兩個步驟。

從 <code>ShopProduct</code> 表中選取出在大阪店（<code>shop_id = '000C'</code>）中銷售的商品（<code>product_id</code>）

從 <code>Product</code> 表中選取出上一步得到的商品（<code>product_id</code>）的銷售單價（<code>sale_price</code>）

SQL 也是如此，同樣要分兩步來完成。首先，第一步如下所示。

因為大阪店的商店編号（<code>shop_id</code>）是“000C”，是以我們可以将其作為條件寫在 <code>WHERE</code> 子句中 [1]。接下來，我們就可以把上述 <code>SELECT</code> 語句作為第二步中的條件來使用了。最終得到的 <code>SELECT</code> 語句請參考代碼清單 36。

代碼清單 36　使用子查詢作為 <code>IN</code> 的參數

如子查詢中的“法則 6”所述，子查詢是從内層開始執行的。是以，該 <code>SELECT</code> 語句也是從内層的子查詢開始執行，然後像下面這樣展開。

這樣就轉換成了之前我們學習過的 <code>IN</code> 的使用方法了吧。可能有些讀者會産生這樣的疑問：“既然子查詢展開後得到的結果同樣是（'0003','0004','0006','0007'），為什麼一定要使用子查詢呢？”

這是因為 <code>ShopProduct</code>（商店商品）表并不是一成不變的。實際上由于各個商店銷售的商品都在不斷發生變化，是以 <code>ShopProduct</code> 表内大阪店銷售的商品也會發生變化。如果 <code>SELECT</code> 語句中沒有使用子查詢的話，一旦商品發生了改變，那麼 <code>SELECT</code> 語句也不得不進行修改，而且這樣的修改工作會變得沒完沒了。

反之，如果在 <code>SELECT</code> 語句中使用了子查詢，那麼即使資料發生了變更，還可以繼續使用同樣的 <code>SELECT</code> 語句。這樣也就減少了我們的正常作業（單純的重複操作）。

像這樣可以完美應對資料變更的程式稱為“易維護程式”，或者“免維護程式”。這也是系統開發中需要重點考慮的部分。希望大家在開始學習程式設計時，就能夠有意識地編寫易于維護的代碼。

<code>IN</code> 的否定形式 <code>NOT IN</code> 同樣可以使用子查詢作為參數，其文法也和 <code>IN</code> 完全一樣。請大家參考代碼清單 37 中的例文。

代碼清單 37　使用子查詢作為 <code>NOT IN</code> 的參數

本例中的 SQL 語句是要選取出“在東京店（000A）以外銷售的商品（<code>product_id</code>）的銷售單價（<code>sale_price</code>）”，“<code>NOT IN</code>”代表了“以外”這樣的否定含義。

我們也像之前那樣來看一下該 SQL 的執行步驟。因為還是首先執行子查詢，是以會得到如下結果。

之後就很簡單了，上述語句應該會傳回 0001 ～ 0003 “以外”的結果。

本文最後将要給大家介紹的是 <code>EXISTS</code> 謂詞。将它放到最後進行學習的原因有以下 3 點。

<code>EXISTS</code> 謂詞

① <code>EXISTS</code> 的使用方法與之前的都不相同

② 文法了解起來比較困難

③ 實際上即使不使用 <code>EXISTS</code>，基本上也都可以使用 <code>IN</code>（或者 <code>NOT IN</code>）來代替

理由 ① 和 ② 都說明 <code>EXISTS</code> 是使用方法特殊而難以了解的謂詞。特别是使用否定形式 <code>NOT EXISTS</code> 的 <code>SELECT</code> 語句，即使是 DB 工程師也常常無法迅速了解。此外，如理由 ③ 所述，使用 <code>IN</code> 作為替代的情況非常多（盡管不能完全替代讓人有些傷腦筋），很多讀者雖然記住了使用方法但還是不能實際運用。

但是一旦能夠熟練使用 <code>EXISTS</code> 謂詞，就能體會到它極大的便利性。是以，非常希望大家能夠在達到 SQL 中級水準時掌握此工具。本文隻簡單介紹其基本使用方法 [2]。

接下來就讓我們趕快看一看 <code>EXISTS</code> 吧。

一言以蔽之，謂詞的作用就是“判斷是否存在滿足某種條件的記錄”。如果存在這樣的記錄就傳回真（<code>TRUE</code>），如果不存在就傳回假（<code>FALSE</code>）。 <code>EXISTS</code>（存在）謂詞的主語是“記錄”。

我們繼續使用前一節“<code>IN</code> 和子查詢”中的示例，使用 <code>EXISTS</code> 選取出“大阪店（000C）在售商品（<code>product_id</code>）的銷售單價（<code>sale_price</code>）”。

<code>SELECT</code> 語句請參考代碼清單 38。

代碼清單 38　使用 <code>EXISTS</code> 選取出“大阪店在售商品的銷售單價”

SQL Server DB2 PostgreSQL MySQL

Oracle 的 <code>FROM</code> 子句中不能使用 <code>AS</code>（會發生錯誤）。是以，在 Oracle 中執行代碼清單 38 時，請将 ① 的部分修改為“<code>FROM Product P</code>”，将 ② 的部分修改為“<code>FROM ShopProduct SP</code>”（删除 <code>FROM</code> 子句中的 <code>AS</code>）

之前我們學過的謂詞，基本上都是像“列 <code>LIKE</code> 字元串”或者“列 <code>BETWEEN</code> 值 1 <code>AND</code> 值 2”這樣需要指定 2 個以上的參數，而 <code>EXISTS</code> 的左側并沒有任何參數。很奇妙吧？這是因為 <code>EXISTS</code> 是隻有 1 個參數的謂詞。<code>EXISTS</code> 隻需要在右側書寫 1 個參數，該參數通常都會是一個子查詢。

上面這樣的子查詢就是唯一的參數。确切地說，由于通過條件“<code>SP.product_id = P.product_id</code>”将 <code>Product</code> 表和 <code>ShopProduct</code> 表進行了聯接，是以作為參數的是關聯子查詢。<code>EXISTS</code> 通常都會使用關聯子查詢作為參數 [3]。

法則 1 通常指定關聯子查詢作為 <code>EXISTS</code> 的參數。

可能大家會覺得子查詢中的 <code>SELECT *</code> 稍微有些不同，就像我們之前學到的那樣，由于 <code>EXISTS</code> 隻關心記錄是否存在，是以傳回哪些列都沒有關系。<code>EXISTS</code> 隻會判斷是否存在滿足子查詢中 <code>WHERE</code> 子句指定的條件“商店編号（<code>shop_id</code>）為 '000C'，商品（<code>Product</code>）表和商店商品（<code>ShopProduct</code>）表中商品編号（<code>product_id</code>）相同”的記錄，隻有存在這樣的記錄時才傳回真（<code>TRUE</code>）。

是以，即使寫成代碼清單 39 那樣，結果也不會發生改變。

代碼清單 39　這樣的寫法也能得到與代碼清單 38 相同的結果

在 Oracle 中執行代碼清單 39 時，請将 ① 的部分修改為“<code>FROM Product P</code>”，将 ② 的部分修改為“<code>FROM ShopProduct SP</code>”（删除 <code>FROM</code> 子句中的 <code>AS</code>）。

大家可以把在 <code>EXISTS</code> 的子查詢中書寫 <code>SELECT *</code> 當作 SQL 的一種習慣。

法則 2 作為 <code>EXISTS</code> 參數的子查詢中經常會使用 <code>SELECT *</code>。

就像 <code>EXISTS</code> 可以用來替換 <code>IN</code> 一樣，<code>NOT IN</code> 也可以用 <code>NOT EXISTS</code> 來替換。下面就讓我們使用 <code>NOT EXISTS</code> 來編寫一條 <code>SELECT</code> 語句，讀取出“東京店（000A）在售之外的商品（<code>product_id</code>）的銷售單價（<code>sale_price</code>）”（代碼清單 40）。

<code>NOT EXISTS</code> 謂詞

代碼清單 40　使用 <code>NOT EXISTS</code> 讀取出“東京店在售之外的商品的銷售單價”

在 Oracle 中執行代碼清單 40 時，請将 ① 的部分修改為“<code>FROM Product P</code>”，将 ② 的部分修改為“<code>FROM ShopProduct SP</code>”（删除 <code>FROM</code> 子句中的 <code>AS</code>）。

<code>NOT EXISTS</code> 與 <code>EXISTS</code> 相反，當“不存在”滿足子查詢中指定條件的記錄時傳回真（<code>TRUE</code>）。

将 <code>IN</code>（代碼清單 36）和 <code>EXISTS</code>（代碼清單 38）的 <code>SELECT</code> 語句進行比較，會得到怎樣的結果呢？可能大多數讀者會覺得 <code>IN</code> 了解起來要容易一些，筆者也認為沒有必要勉強使用 <code>EXISTS</code>。因為 <code>EXISTS</code> 擁有 <code>IN</code> 所不具有的便利性，嚴格來說兩者并不相同，是以希望大家能夠在中級篇中掌握這兩種謂詞的使用方法。

各種各樣的函數

SQL 謂詞

CASE 表達式

（完）

雖然使用“<code>shop_name='大阪'</code>”作為條件可以得到同樣的結果，但是通常情況下，指定資料庫中的商店或者商品時，并不會直接使用商品名稱。這是因為與編号比起來，名稱更有可能發生改變。 ↩︎

希望了解 <code>EXISTS</code> 謂詞詳細内容的讀者，可以參考《SQL進階教程》中 1-8 節的内容。 ↩︎

雖然嚴格來說文法上也可以使用非關聯子查詢作為參數，但實際應用中幾乎沒有這樣的情況。 ↩︎