Wednesday, July 4, 2012

Find values in one table that dont exist in another in MySQL

So the problem is that you have two tables say A and B and you need to find the records that exist in table B which don't exist in table A. A very common requirement while you work with the database. There are multiple ways of doing this.
1. Use a left join query
2. Use a not in query
3. Use a not exists query

The link http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/
explains each method in more detail. I've always struggled with which way is faster. Sometimes I go with whichever I find convenient. But looks like the link says the left join approach is the fastest.

Regardless, one has to make sure to use "explain" with your query to check if you are using indexes in your query plan. Queries that don't use indexes are slow running especially if you have tons of rows to join.

No comments:

Post a Comment