在 PostgreSQL 中對子查詢使用 NOT IN

1. 在 PostgreSQL 中將 NOT IN 運算子與子查詢一起使用
2. 使用 NOT EXISTS 運算子作為更好的選擇

NOT IN 中的 NOT 反轉了簡單使用 IN 運算子的結果。NOT IN 運算子的右側必須有一個子查詢，其中返回多個列以檢查表示式是否與資料匹配。

如果在返回的子查詢資料中找不到表示式，則 NOT IN 傾向於返回 true。

讓我們繼續嘗試瞭解在 PostgreSQL 中使用標準 SQL NOT IN 時使用者面臨的一些常見問題和問題。

在 PostgreSQL 中將 NOT IN 運算子與子查詢一起使用

在 PostgreSQL 中，如果你使用 NOT IN 來確保沒有任何表示式匹配特定的資料集，則必須確保返回的子查詢資料中沒有 NULL 值。

這到底是什麼意思？

讓我們繼續嘗試藉助示例來理解這一點。我們將建立一個包含兩列的表 HORSE； ID 和 顏色。

CREATE TABLE horse (
    ID int PRIMARY KEY,
    Colour TEXT
);

現在讓我們也插入一些值。

INSERT INTO horse values (1, 'black'), (2, 'brown'), (3, 'white');

讓我們繼續為 RIDER 建立另一個表。

CREATE TABLE rider (
    ID int PRIMARY KEY,
    horse_id int
);

你可以在 PGADMIN 和 PSQL 中執行上面給出的任何程式碼。

讓我們在兩個表中插入一些值：

INSERT INTO horse values (1, 'black'), (2, 'brown'), (3, 'white');

INSERT INTO rider values (1, 1), (2, 2), (3, 4)

在這裡，你可以看到 RIDER 中的 id 3 有馬 4，這在 HORSE 表中不存在。我們這樣做是為了確保在我們的示例中使用 NOT IN。

假設我們想從 RIDER 表中刪除這個 RIDER 3。我們如何做到這一點？

select * from rider
where horse_id not in (select id from horse)

輸出：

現在，如果在 HORSE 表中我們也有一些空值，而不是乾淨的值，該怎麼辦。讓我們修改 HORSE 表的 INSERT 語句。

INSERT INTO horse values (1, 'black'), (2, 'brown'), (3, 'white'), (NULL, NULL);

附帶說明，當在主鍵列中插入 NULL 值時，請從表中刪除主鍵約束以允許插入 NULL。

在這裡，我們的 NULL HORSE 有一個 NULL Color，所以當我們像上面那樣執行 NOT IN 的查詢時，我們得到以下資訊：

輸出：

那麼剛剛發生了什麼？它不應該返回 ID 4，因為 HORSE 表中不存在？

好吧，讓我們瞭解一下 NOT IN 是如何工作的。NOT IN 運算子使用 AND 運算子。如果要搜尋的所有行都返回 true，它將返回 true。

所以像這樣的東西可以代替 NOT IN：

NOT IN (ROW 1) AND NOT IN (ROW 2) AND NOT IN (ROW 3) .....

在我們的例子中，前三個資料集的 NOT IN 返回 true，它不會為 NULL 列返回任何值，因為 PostgreSQL 文件引用：

If all the per-row results are either unequal or null, with at least one null, then the result of `NOT IN` is null

從 NOT IN 返回 NULL 會導致所有其他 true 為 false，因此我們的表不返回任何內容。

我們如何解決這個問題？

第一種方法是防止向表中插入任何 NULL。儘管如此，如果我們已經在資料庫中建立了表並且現在想要執行查詢來獲取資料，那麼這也是沒有用的。

因此，我們必須尋找其他解決方案來有效地解決這個問題。

使用 NOT EXISTS 運算子作為更好的選擇

讓我們使用以下查詢：

select horse_id from rider r
where not exists
(select id from horse h
where h.id = r.horse_id)

即使存在空值，這也傾向於最終將值 4 返回給我們，並且是一種有效的策略。讓我們看看它是如何工作的。

如果子查詢返回任何內容，即任何單行，則 EXISTS 子句返回 true，否則返回 false。因此，當我們想要找到丟失的 HORSE 時，我們傾向於從 HORSE 表中返回與 RIDER 表中的 IDs 相等的值。

子查詢返回多行，並且 EXISTS 變為真，使 NOT EXISTS FALSE。

最終，我們的最終查詢從 RIDER 中選擇 HORSE_ID，它不等於 HORSE 表中提供的 IDs。在我們的例子中，即 4，因此我們的查詢完美執行。

但是，NOT EXISTS 在用於 NOT IN 運算子時會導致效能損失。

select horse_id from rider
full join horse on rider.horse_id = horse.ID
where horse.ID is null

輸出：

所以它返回一個 NULL 和我們沒有找到的值； 4。如何？

當我們在兩個 ID 相似的條件下進行完全連線時，它也傾向於返回不相似的行。它將返回來自 HORSE 的集合 (NULL, NULL) 和來自 RIDER 的 (3, 4)，因為它們都是不匹配的。

因此，我們可以利用這一點並在最後寫入 NULL 條件以返回這些不匹配的行。

當我們寫下 Horse.ID is NULL 時，它將從為 NULL 的騎手中選擇 HORSE_ID。在這種情況下，第一個集合 (NULL, NULL) 被包括在內；集合 (3,4) 也是如此。為什麼？

該集合不包含 NULL，但也是不匹配的。所以我們的 FULL JOIN 在它的返回表中也設定了 NULL 值。

因此，我們得到這個值作為結果輸出給我們。

今天我們研究了為包含 NULL 的值實現 NOT IN 運算子。我們希望這對你有所幫助並擴充套件你的知識庫。