Using 'NOT IN' in MySQL

Suppose we have a table called USERS that has a primary key 'id' and another table SUBSCRIPTIONS with foreign key 'uid'.

If we want to remove all subscriptions (from the SUBSCRIPTIONS table) that does not correspond to any user, we can run this MySQL query:

DELETE FROM SUBSCRIPTIONS WHERE uid NOT IN (SELECT id from USERS)

What we are doing here is getting a list of all the ids from the USERS table and then deleting all rows from the SUBSCRIPTIONS table where the user id (uid) does not correspond to any id in the USERS table. Very simple.

Note: Throughout this blog, you will find many books that are for sale on Amazon. I recommend you get these books while learning web programming as they will help strengthening your skills.