如何在 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 中最快的方法。

<code class="language-sql">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
);</code>

此查询有效地检查 ip_location 中的每个 IP 是否存在于 login_log 中对应的 IP。 子查询中的 SELECT 1 可以优化性能。

2。 LEFT JOIN 与 IS NULL： 简洁且通常快速的替代方案。

<code class="language-sql">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;</code>

这将执行左连接，保留来自 login_log 的所有行。 ip_location 中缺少的 IP 地址将在 NULL 列中包含 i.ip，然后通过 WHERE 子句进行过滤。

3。 EXCEPT ALL: 一种紧凑的方法，但对于复杂查询来说效率可能较低。

<code class="language-sql">SELECT ip
FROM   login_log
EXCEPT ALL -- "ALL" preserves duplicates, potentially faster
SELECT ip
FROM   ip_location;</code>

这个集合操作直接找出两组IP地址之间的差异。 EXCEPT ALL 更可取，因为它速度更快并且可以处理重复的 IP。

4。 NOT IN（谨慎使用）： 虽然可以使用，但 NOT IN 可能会很慢，尤其是在处理大型数据集时。 它通常比以前的方法效率低。

<code class="language-sql">SELECT ip
FROM   login_log
WHERE  ip NOT IN (
    SELECT DISTINCT ip -- DISTINCT is optional but can improve performance slightly
    FROM   ip_location
);</code>

此方法直接检查 IP 是否不出现在来自 ip_location 的 IP 列表中。然而，随着数据集的增大，性能可能会显着下降。

进一步阅读：

要深入了解这些技术和相关 PostgreSQL 优化策略，请参阅以下资源：

• EXISTS 子查询中什么更容易阅读？
• 查找不存在 join 的记录
• 选择第二列的值不存在于第一列中的行

以上是如何在 PostgreSQL 中查找一个表中另一表中缺少的 IP 地址？的详细内容。更多信息请关注PHP中文网其他相关文章！