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

I have two tables with binding primary key in database and I desire to find a disjoint set between them. For example,

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

• Table1 has columns (ID, Name) and sample data: (1 ,John), (2, Peter), (3, Mary)
• 表1有列(ID, Name)和示例数据:(1,John)， (2, Peter)， (3, Mary)
• Table2 has columns (ID, Address) and sample data: (1, address2), (2, address2)
• 表2有列(ID、地址)和示例数据:(1,address2)， (2, address2)

So how do I create a SQL query so I can fetch the row with ID from table1 that is not in table2. In this case, (3, Mary) should be returned?

如何创建SQL查询，以便从表1中获取ID不在表2中的行。在这种情况下，(3,Mary)应该返回吗?

Ps. The ID is the primary key for those two tables.

ID是这两个表的主键。

Thanks in advance.

提前谢谢。

5 个解决方案

#1

Try this

试试这个

SELECT ID, Name 
FROM   Table1 
WHERE  ID NOT IN (SELECT ID FROM Table2)

#2

Use LEFT JOIN

使用左连接

SELECT  a.*
FROM    table1 a
            LEFT JOIN table2 b
                on a.ID = b.ID
WHERE   b.id IS NULL

#3

Fast Alternative

I ran some tests (on postgres 9.5) using two tables with ~2M rows each. This query below performed at least 5* better than the other queries proposed:

我在postgres9.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;

#4

Keeping in mind the points made in @John Woo's comment/link above, this is how I typically would handle it:

记住@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
)

#5

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