在 PostgreSQL 中對子查詢使用 NOT IN
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
運算子。我們希望這對你有所幫助並擴充套件你的知識庫。
Hello, I am Bilal, a research enthusiast who tends to break and make code from scratch. I dwell deep into the latest issues faced by the developer community and provide answers and different solutions. Apart from that, I am just another normal developer with a laptop, a mug of coffee, some biscuits and a thick spectacle!
GitHub