Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select from one table where not in another

Tags:

sql

mysql

I'm trying to find the rows that are in one table but not another, both tables are in different databases and also have different column names on the column that I'm using to match.

I've got a query, code below, and I think it probably works but it's way too slow:

SELECT `pm`.`id` FROM `R2R`.`partmaster` `pm` WHERE NOT EXISTS (     SELECT *      FROM `wpsapi4`.`product_details` `pd`     WHERE `pm`.`id` = `pd`.`part_num` ) 

So the query is trying to do as follows:

Select all the ids from the R2R.partmaster database that are not in the wpsapi4.product_details database. The columns I'm matching are partmaster.id & product_details.part_num

like image 815
Drahcir Avatar asked Sep 29 '11 10:09

Drahcir


People also ask

How can I get data from one table is not in another table?

We can get the records in one table that doesn't exist in another table by using NOT IN or NOT EXISTS with the subqueries including the other table in the subqueries.

How do you SELECT all records from one table that do not exist in another table Linq?

Users select e). ToList(); var result2 = (from e in db.Fi select e). ToList(); List<string> listString = (from e in result1 where !( from m in result2 select m.


1 Answers

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.

like image 116
Johan Avatar answered Sep 21 '22 05:09

Johan