从一个表中查找另一个表中不存在的记录 [英] Find records from one table which don't exist in another

问题描述

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

I've got the following two tables (in 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 中的人拨打的?所需的输出是:

How do I find out which calls were made by people whose phone_number is not in the Phone_book? The desired output would be:

Call
+----+------+--------------+
| id | date | phone_number |
+----+------+--------------+
| 3  | 1045 | 333333333333 |
+----+------+--------------+

推荐答案

有几种不同的方法可以做到这一点，效率各不相同，具体取决于您的查询优化器的性能以及两个表的相对大小:

There's several different ways of doing this, with varying efficiency, depending on how good your query optimiser is, and the relative size of your two tables:

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

This is the shortest statement, and may be quickest if your phone book is very short:

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

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

(ignoring that, as others have said, it's normally best to select just the columns you want, not '*')

这篇关于从一个表中查找另一个表中不存在的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！