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

Answer 3

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)

Answer 4

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.

Answer 5

I think

SELECT CALL.* FROM CALL LEFT JOIN Phone_book ON 
CALL.id = Phone_book.id WHERE Phone_book.name IS NULL

Answer 6

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

Answer 7

SELECT name, phone_number FROM Call a
WHERE a.phone_number NOT IN (SELECT b.phone_number FROM Phone_book b)

Answer 8

Alternatively,

select id from call
minus
select id from phone_number

Answer 9

Don't forget to check your indexes!

If your tables are quite large you'll need to make sure the phone book has an index on the phone_number field. With large tables the database will most likely choose to scan both tables.

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

You should create indexes both Phone_Book and Call containing the phone_number. If performance is becoming an issue try an lean index like this, with only the phone number:

The fewer fields the better since it will have to load it entirely. You'll need an index for both tables.

ALTER TABLE [dbo].Phone_Book ADD CONSTRAINT [IX_Unique_PhoneNumber] UNIQUE NONCLUSTERED 
(
    Phone_Number
)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON) ON [PRIMARY]
GO

If you look at the query plan it will look something like this and you can confirm your new index is actually being used. Note this is for SQL Server but should be similar for MySQL.

With the query I showed there's literally no other way for the database to produce a result other than scanning every record in both tables.