如何在 PostgreSQL 中尋找一個表中另一表中缺少的 IP 位址？

本文示範如何有效辨識 PostgreSQL 資料庫中 login_log 表中存在但 ip_location 表中缺少的 IP 位址。 提出了幾種方法，每種方法都有自己的優點和缺點。

辨識遺失的 IP 位址

核心問題涉及尋找兩個表之間的差異：login_log（包含使用 IP 位址的登入嘗試）和 ip_location（包含一組已知的 IP 位址）。目標是提取login_log中記錄的未記錄在ip_location中的IP位址。

最優查詢方法

幾種 SQL 技術可以實現這一點；以下是最有效且最常用的：

1。 NOT EXISTS 子查詢： 通常是 PostgreSQL 中最快的方法。

SELECT ip
FROM   login_log l
WHERE  NOT EXISTS (
    SELECT 1 -- SELECT list is irrelevant, 1 is efficient
    FROM   ip_location i
    WHERE  i.ip = l.ip
);

此查詢有效地檢查 ip_location 中的每個 IP 是否存在於 login_log 中對應的 IP。 子查詢中的 SELECT 1 可以最佳化效能。

2。 LEFT JOIN 與 IS NULL： 簡潔且通常快速的替代方案。

SELECT l.ip
FROM   login_log l
LEFT   JOIN ip_location i USING (ip) -- Equivalent to ON i.ip = l.ip
WHERE  i.ip IS NULL;

這將執行左連接，保留來自 login_log 的所有行。 ip_location 中缺少的 IP 位址將在 NULL 列中包含 i.ip，然後透過 WHERE 子句進行過濾。

3。 EXCEPT ALL: 一種緊湊的方法，但對於複雜查詢來說效率可能較低。

SELECT ip
FROM   login_log
EXCEPT ALL -- "ALL" preserves duplicates, potentially faster
SELECT ip
FROM   ip_location;

這個集合運算直接找出兩組IP位址之間的差異。 EXCEPT ALL 更可取，因為它速度更快並且可以處理重複的 IP。

4。 NOT IN（謹慎使用）： 雖然可以使用，但 NOT IN 可能會很慢，尤其是在處理大型資料集時。 它通常比以前的方法效率低。

SELECT ip
FROM   login_log
WHERE  ip NOT IN (
    SELECT DISTINCT ip -- DISTINCT is optional but can improve performance slightly
    FROM   ip_location
);

此方法直接檢查 IP 是否不出現在來自 ip_location 的 IP 清單中。然而，隨著數據集的增大，效能可能會顯著下降。

進一步閱讀：

要深入了解這些技術和相關 PostgreSQL 最佳化策略，請參考以下資源：

• EXISTS 子查詢什麼比較容易閱讀？
• 找出不存在 join 的記錄
• 選擇第二列的值不存在於第一列的行

以上是如何在 PostgreSQL 中尋找一個表中另一表中缺少的 IP 位址？的詳細內容。更多資訊請關注PHP中文網其他相關文章！