Blog | Status Updates | About | Contact | Advertise

August 4, 2005 / Filed under: SQL

When working with MySQL, sometimes it’s good to check which rows exist in one table, that don’t exist in another. This is only possible when you have two related tables. In other words, a primary key in one table, and a foreign key in the other table.

If one table has rows that the other table does not, a match (between the two related tables) will not exist for those "hanging rows." It’s best to remove "hanging rows," to keep your data organized.

So, here’s the break-down of what has to be done, in plain English:

I have to go into each table, and cross check each row, against one another. So, for each row that exists in the first table, I have to make sure there is a match in the second table. This could be incredibly time consuming, as there are hundreds of rows in both tables.

Forget checking each row, one by one. MySQL will do the cross-check, and present you with the result, all with a single query:

SELECT table_1.row_1, table_2.row_1
FROM table_2
LEFT JOIN table1 ON table_1.row_1 = table_2.row_1
WHERE table_1.row_1 IS NULL

This is the most basic approach, but extremely effective. There are a few key things to note here, which differ from normal queries.

1. There is only one table specified in the FROM clause, even though we are checking two tables. The table that should be specified in the FROM clause is the one that you want to "clean up." In other words - we’d like to remove the "hanging rows" from table_2.
2. Notice that, although we only specify table_2 in the FROM clause, we still specify a row from table_1, in the SELECT clause. This may seem odd to some, but the next step will explain.
3. The LEFT JOIN syntax involves the other table that we are cross-checking against - in this case, table_1.
4. The ON clause allows us to specify the primary key relation to the foreign key, across the two tables. In other words, this is where the "cross check" happens. MySQL compares all of the primary keys against all of the foreign keys (which is what we would be doing manually).
5. When MySQL finds foreign keys that don’t match primary keys, it returns a NULL value.
6. The WHERE clause specifies that we only want to see those NULL values. Because... those NULL values are the rows that exist in one table, but not the other. That’s the information we’re after.