SQL-从一个表中查找另一个表中不存在的记录 [英] SQL - find records from one table which don't exist in another
问题描述
我有以下两个SQL表(在MySQL中):
I've got the following two SQL 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 '*
')
这篇关于SQL-从一个表中查找另一个表中不存在的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!