MySQL SQL - 从一个表中查找另一个表中不存在的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/367863/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - find records from one table which don't exist in another
提问by Philip Morton
I've got the following two SQL tables (in MySQL):
我有以下两个 SQL 表（在 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 |
+----+------+--------------+
How do I find out which calls were made by people whose phone_numberis not in the Phone_book? The desired output would be:
如何找出哪些电话是由人，他们提出phone_number是不是在Phone_book？所需的输出是：
Call
+----+------+--------------+
| id | date | phone_number |
+----+------+--------------+
| 3 | 1045 | 333333333333 |
+----+------+--------------+
Any help would be much appreciated.
任何帮助将非常感激。
回答by Alnitak
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)
alternatively (thanks to Alterlife)
或者（感谢Alterlife）
SELECT *
FROM Call
WHERE NOT EXISTS
(SELECT *
FROM Phone_book
WHERE Phone_book.phone_number = Call.phone_number)
or (thanks to WOPR)
或（感谢 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 '*')
（忽略这一点，正如其他人所说，通常最好只选择您想要的列，而不是“ *”）
回答by WOPR
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
Should remove the subquery, allowing the query optimiser to work its magic.
应该删除子查询，让查询优化器发挥它的魔力。
Also, avoid "SELECT *" because it can break your code if someone alters the underlying tables or views (and it's inefficient).
此外，请避免使用“SELECT *”，因为如果有人更改了基础表或视图（而且效率低下），它可能会破坏您的代码。
回答by Alterlife
The code below would be a bit more efficient than the answers presented above when dealing with larger datasets.
在处理更大的数据集时，下面的代码会比上面给出的答案更有效。
SELECT * FROM Call WHERE
NOT EXISTS (SELECT 'x' FROM Phone_book where
Phone_book.phone_number = Call.phone_number)
回答by Vlado
SELECT DISTINCT Call.id
FROM Call
LEFT OUTER JOIN Phone_book USING (id)
WHERE Phone_book.id IS NULL
This will return the extra id-s that are missing in your Phone_book table.
这将返回 Phone_book 表中缺少的额外 id-s。
回答by Nat Geo
I think
我认为
SELECT CALL.* FROM CALL LEFT JOIN Phone_book ON
CALL.id = Phone_book.id WHERE Phone_book.name IS NULL
回答by Harvinder Sidhu
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
回答by JoshYates1980
SELECT name, phone_number FROM Call a
WHERE a.phone_number NOT IN (SELECT b.phone_number FROM Phone_book b)
回答by elfekz
Alternatively,
或者，
select id from call
minus
select id from phone_number
