本文翻译自:SQL - find records from one table which don't exist in another
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_number
is not in the Phone_book
? 如何找出哪些电话是由人,他们做出phone_number
是不是在Phone_book
? The desired output would be: 所需的输出将是:
Call
+----+------+--------------+
| id | date | phone_number |
+----+------+--------------+
| 3 | 1045 | 333333333333 |
+----+------+--------------+
Any help would be much appreciated. 任何帮助将非常感激。
#1楼
参考:https://stackoom.com/question/1xHh/SQL-从一个表中查找另一个表中不存在的记录
#2楼
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。
#3楼
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
#4楼
SELECT name, phone_number FROM Call a
WHERE a.phone_number NOT IN (SELECT b.phone_number FROM Phone_book b)
#5楼
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 ' *
') (如其他人所说,忽略它通常最好只选择想要的列,而不是' *
')
#6楼
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)