SQL 查询以查找 ID 不在另一个表中的记录

在数据库中有两个具有绑定主键的表，我希望在它们之间找到一个不相交的集合。比如说,

  • Table1具有列(ID, Name)和示例数据: (1 ,John), (2, Peter), (3, Mary)
  • Table2具有列(ID, Address)和示例数据: (1, address2), (2, address2)

因此，我如何创建一个 SQL 查询，以便能够从 table1中获取不在 table2中的具有 ID 的行。在这种情况下，应该返回 (3, Mary)吗？

PS: ID 是这两个表的主键。

323856 次浏览
小开

使用 LEFT JOIN

SELECT  a.*
FROM    table1 a
LEFT JOIN table2 b
on a.ID = b.ID
WHERE   b.id IS NULL
小开
最佳答案

试试这个

SELECT ID, Name
FROM   Table1
WHERE  ID NOT IN (SELECT ID FROM Table2)
小开 
SELECT COUNT(ID) FROM tblA a
WHERE a.ID NOT IN (SELECT b.ID FROM tblB b)    --For count




SELECT ID FROM tblA a
WHERE a.ID NOT IN (SELECT b.ID FROM tblB b)    --For results
小开

快速选择

我运行了一些测试(在 postgres 9.5上) ，使用了两个表，每个表大约有2M 行。下面的查询至少比提议的其他查询执行得好5 * :

-- Count
SELECT count(*) FROM (
(SELECT id FROM table1) EXCEPT (SELECT id FROM table2)
) t1_not_in_t2;


-- Get full row
SELECT table1.* FROM (
(SELECT id FROM table1) EXCEPT (SELECT id FROM table2)
) t1_not_in_t2 JOIN table1 ON t1_not_in_t2.id=table1.id;
小开

记住@John Woo 在上面的评论/链接中提到的观点，我通常是这样处理的:

SELECT t1.ID, t1.Name
FROM   Table1 t1
WHERE  NOT EXISTS (
SELECT TOP 1 NULL
FROM Table2 t2
WHERE t1.ID = t2.ID
)
小开

基本上有3种方法: not exists，not in和 left join / is null。

左连接为空

SELECT  l.*
FROM    t_left l
LEFT JOIN
t_right r
ON      r.value = l.value
WHERE   r.value IS NULL

不在

SELECT  l.*
FROM    t_left l
WHERE   l.value NOT IN
(
SELECT  value
FROM    t_right r
)

不存在

SELECT  l.*
FROM    t_left l
WHERE   NOT EXISTS
(
SELECT  NULL
FROM    t_right r
WHERE   r.value = l.value
)

哪个更好？这个问题的答案最好分解为主要的特定 RDBMS 供应商。一般来说，当子查询中的记录数量未知时，应该避免使用 select ... where ... in (select...)。一些供应商可能会限制其大小。例如，Oracle 有一个 最高限额为1000。最好的办法是尝试这三种方法，并展示执行计划。

特别是在 PostgreSQL 中，NOT EXISTS和 LEFT JOIN / IS NULL的执行计划是相同的。我个人更喜欢 NOT EXISTS选项，因为它能更好地显示意图。毕竟语义是 您希望在 A 中找到它的 pk < strong > 在 B 中不存在 的记录。

旧的，但仍然是黄金，特定于 PostgreSQL 虽然: https://explainextended.com/2009/09/16/not-in-vs-not-exists-vs-left-join-is-null-postgresql/


提交答案