Find records from one table which don't exist in another

Question

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 | +----+------+--------------+ 

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 | +----+------+--------------+ 

Answer 1

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)

SELECT * FROM   Call WHERE  NOT EXISTS   (SELECT *    FROM   Phone_book    WHERE  Phone_book.phone_number = Call.phone_number) 

or (thanks to 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 '*')

Answer 2

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).