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

by

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 ‘*‘)

More Related Contents:

  1. MySQL LIKE IN()?
  2. Why would an IN condition be slower than “=” in sql?
  3. PostgreSQL GROUP BY different from MySQL?
  4. GROUP BY behavior when no aggregate functions are present in the SELECT clause
  5. MySQL dump by query
  6. SQLite – ORDER BY RAND()
  7. Delete sql rows where IDs do not have a match from another table
  8. In SQL / MySQL, what is the difference between “ON” and “WHERE” in a join statement?
  9. What does DELIMITER // do in a Trigger?
  10. Does Mysql have an equivalent to @@ROWCOUNT like in mssql?
  11. MySQL equivalent of DECODE function in Oracle
  12. How do you version your database schema? [closed]
  13. MySQL, Get users rank
  14. Mysql: how to select groups having certain values?
  15. How to fill date gaps in MySQL?
  16. Adding a new SQL column with a default value
  17. Boolean vs tinyint(1) for boolean values in MySQL
  18. MySQL full text search for words with three or less letters
  19. MySQL: “The SELECT would examine more than MAX_JOIN_SIZE rows”
  20. Function for week of the month in mysql
  21. MySQL’s alternative to T-SQL’s WITH TIES
  22. Find rows that have the same value on a column in MySQL
  23. what is the difference between GROUP BY and ORDER BY in sql
  24. Two single-column indexes vs one two-column index in MySQL?
  25. VARCHAR as foreign key/primary key in database good or bad?
  26. MySQL SELECT x FROM a WHERE NOT IN ( SELECT x FROM b ) – Unexpected result
  27. MySql bulk load command line tool
  28. ALTER TABLE without locking the table?
  29. MySQL: Typecasting NULL to 0
  30. How to get the byte size of resultset in an SQL query?

Leave a Comment