从一个表中查找另一个表中不存在的记录

我有以下两个表(在MySQL):

Phone_book
+----+------+--------------+
| id | name | phone_number |
+----+------+--------------+
| 1  | John | 111111111111 |
+----+------+--------------+
| 2  | Jane | 222222222222 |
+----+------+--------------+


Call
+----+------+--------------+
| id | date | phone_number |
+----+------+--------------+
| 1  | 0945 | 111111111111 |
+----+------+--------------+
| 2  | 0950 | 222222222222 |
+----+------+--------------+
| 3  | 1045 | 333333333333 |
+----+------+--------------+

我如何找出哪些调用是由phone_number不在Phone_book中的人发出的?期望的输出将是:

Call
+----+------+--------------+
| id | date | phone_number |
+----+------+--------------+
| 3  | 1045 | 333333333333 |
+----+------+--------------+
520391 次浏览
小开
最佳答案

有几种不同的方法来做到这一点，不同的效率，这取决于你的查询优化器有多好，以及你的两个表的相对大小:

这是最短的语句，如果你的电话簿很短，可能是最快的:

SELECT  *
FROM    Call
WHERE   phone_number NOT IN (SELECT phone_number FROM Phone_book)

或者(感谢Alterlife)

SELECT *
FROM   Call
WHERE  NOT EXISTS
(SELECT *
FROM   Phone_book
WHERE  Phone_book.phone_number = Call.phone_number)

或者(感谢WOPR)

SELECT *
FROM   Call
LEFT OUTER JOIN Phone_Book
ON (Call.phone_number = Phone_book.phone_number)
WHERE Phone_book.phone_number IS NULL

(忽略这一点，正如其他人所说，通常最好只选择你想要的列，而不是'*')

小开

当处理更大的数据集时，下面的代码将比上面给出的答案更有效一些。

SELECT *
FROM Call
WHERE NOT EXISTS (
SELECT 'x'
FROM Phone_book
WHERE Phone_book.phone_number = Call.phone_number
);
小开 
SELECT Call.ID, Call.date, Call.phone_number
FROM Call
LEFT OUTER JOIN Phone_Book
ON (Call.phone_number=Phone_book.phone_number)
WHERE Phone_book.phone_number IS NULL

应该删除子查询，允许查询优化器发挥它的魔力。

另外，避免使用“SELECT *”，因为如果有人更改了底层表或视图，它会破坏代码(而且效率很低)。

小开

我认为

SELECT CALL.* FROM CALL LEFT JOIN Phone_book ON
CALL.id = Phone_book.id WHERE Phone_book.name IS NULL
小开 
SELECT DISTINCT Call.id
FROM Call
LEFT OUTER JOIN Phone_book USING (id)
WHERE Phone_book.id IS NULL

这将返回Phone_book表中缺少的额外id。

小开 
SELECT t1.ColumnID,
CASE
WHEN NOT EXISTS( SELECT t2.FieldText
FROM Table t2
WHERE t2.ColumnID = t1.ColumnID)
THEN t1.FieldText
ELSE t2.FieldText
END FieldText
FROM Table1 t1, Table2 t2
小开 
SELECT name, phone_number FROM Call a
WHERE a.phone_number NOT IN (SELECT b.phone_number FROM Phone_book b)
小开

另外,

select id from call
minus
select id from phone_number
小开

别忘了检查你的索引!

如果你的表非常大，你需要确保电话簿在phone_number字段上有一个索引。对于大表，数据库很可能选择扫描两个表。

SELECT *
FROM   Call
WHERE  NOT EXISTS
(SELECT *
FROM   Phone_book
WHERE  Phone_book.phone_number = Call.phone_number)

你应该创建索引Phone_Book和Call，其中包含phone_number。如果性能成为一个问题，尝试一个像这样的精益索引，只有电话号码:

字段越少越好，因为它必须完全加载它。这两个表都需要一个索引。

ALTER TABLE [dbo].Phone_Book ADD CONSTRAINT [IX_Unique_PhoneNumber] UNIQUE NONCLUSTERED
(
Phone_Number
)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON) ON [PRIMARY]
GO

如果您查看查询计划，它将看起来像这样，您可以确认您的新索引实际上正在使用。注意，这是针对SQL Server的，但应该与MySQL类似。

对于查询，我展示了除了扫描两个表中的每条记录之外，数据库实际上没有其他方法来生成结果。

enter image description here


提交答案