MySQL: Find records in one table that are not in another

There may be times you need to check if there are records in one table that are not in another for example to check for data integrity or simply to find out if e.g. there are any products not assigned to a category or webpages to a tag. This post looks at how to do this with MySQL.

Revised Post

Please note that I have revised and republished this post here which uses the more efficient NOT EXISTS query. Both methods are documented in the MySQL manual but the NOT EXISTS one appears to run faster from my limited amount of benchmarking. Thanks to Greg Jorgensen for advising my about the better way of acheiving this.

Example tables

The examples below use three tables as follows:

content: contains the content pages for a website. The primary key is content_id.

tags: the "tags" that a page is tagged with. The primary key is tag_id.

content_to_tags: a table that creates a many-to-many relationship between the above two tables; a page can belong to multiple tags.

Example 1

The first example finds records in content_to_tags that have no associated record in content. This could have happened if the application deleted a record from content but didn’t delete the associated records from content_to_tags:

SELECT *
FROM content_to_tags c2t
LEFT JOIN content c ON c2t.content_id = c.content_id
WHERE c.content_id IS NULL

Example 2

This is the same as for the first example but comparing tags with content_to_tags. Again, this might have happened if records were deleted from tags but not their associated records from content_to_tags:

SELECT *
FROM content_to_tags c2t
LEFT JOIN tags t ON c2t.tag_id = t.tag_id
WHERE t.tag_id IS NULL

Example 3

The next example looks for records in content where there are no associated records in content_to_tags. This is useful if you want to find any untagged posts:

SELECT *
FROM content c
LEFT JOIN content_to_tags c2t ON c.content_id = c2t.content_id
WHERE c2t.content_id IS NULL

Example 4

The final example is the same as the above but to find tags that have no associated records in content_to_tags. This is useful for finding tags that have no content, or in another context (with different table names etc) categories that have no products in them:

SELECT *
FROM tags t
LEFT JOIN content_to_tags c2t ON t.tag_id = c2t.tag_id
WHERE c2t.tag_id IS NULL

My next MySQL post (this time next week) will look at how to delete records from one table where there are no associated records in the other table. I have covered this before in an earlier post (5 years ago!) but the table aliasing syntax for doing this must have changed slightly in more recent MySQL versions; my new post will cover an error when using the suggested SQL from that post with a current MySQL version.