Select from one table where not in another

by

Expanding on Sjoerd’s anti-join, you can also use the easy to understand SELECT WHERE X NOT IN (SELECT) pattern.

SELECT pm.id FROM r2r.partmaster pm
WHERE pm.id NOT IN (SELECT pd.part_num FROM wpsapi4.product_details pd)

Note that you only need to use ` backticks on reserved words, names with spaces and such, not with normal column names.

On MySQL 5+ this kind of query runs pretty fast.
On MySQL 3/4 it’s slow.

Make sure you have indexes on the fields in question
You need to have an index on pm.id, pd.part_num.

More Related Contents:

  1. Recreating a query using INNER JOIN? [duplicate]
  2. Create a Cumulative Sum Column in MySQL
  3. Equivalent of explode() to work with strings in MySQL
  4. How can I tell when a MySQL table was last updated?
  5. MySQL “WITH” clause
  6. mysql select dynamic row values as column names, another column as value
  7. Operand Should Contain 1 Column – MySQL NOT IN
  8. MySQL DELETE FROM with subquery as condition
  9. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  10. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  11. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  12. Simple Random Samples from a Sql database
  13. MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB
  14. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  15. How do I use properly CASE..WHEN in MySQL
  16. Determine Rank based on Multiple Columns in MySQL
  17. Checking multiple columns for one value
  18. What is better in MYSQL count(*) or count(1)?
  19. Delete query not working in mysql
  20. How to insert data to MySQL with auto-incremented column(field)?
  21. SQL unique varchar case sensitivity question
  22. How to get current date & time in MySQL?
  23. How do I count columns of a table
  24. Laravel join with 3 Tables
  25. How to regex in a MySQL query
  26. MySQL Group By Hours
  27. MySQL 5.6 – DENSE_RANK like functionality without Order By
  28. Syntax error on MERGE statement
  29. #1060 – Duplicate column name ‘id’
  30. Update multiple rows with multiple ‘where’ clauses for each individual row

Leave a Comment