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

爱被打了一巴掌 2022-11-28 13:42 211阅读 0赞

本文翻译自：[SQL - find records from one table which don't exist in another][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-从一个表中查找另一个表中不存在的记录][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)

[SQL - find records from one table which don_t exist in another]: https://oldbug.net/q/1xHh/SQL-find-records-from-one-table-which-don-t-exist-in-another
[https_stackoom.com_question_1xHh_SQL-]: https://stackoom.com/question/1xHh/SQL-%E4%BB%8E%E4%B8%80%E4%B8%AA%E8%A1%A8%E4%B8%AD%E6%9F%A5%E6%89%BE%E5%8F%A6%E4%B8%80%E4%B8%AA%E8%A1%A8%E4%B8%AD%E4%B8%8D%E5%AD%98%E5%9C%A8%E7%9A%84%E8%AE%B0%E5%BD%95
[Alterlife]: https://stackoverflow.com/users/36848/alterlife