Finding missing records of the right joined table in MySQL

Let’s say you have two database tables that are supposed to have the same record IDs. Perhaps they contain different data but share the same ID — for this example, we call it “user_id”. This query will select only the missing records of the right joined table.

SELECT a.user_id, a.other_detail, b.user_id FROM tablea a LEFT JOIN tableb b ON a.user_id=b.user_id WHERE b.user_id IS NULL

Since the “right” table is joining the “left” table with a left join, any missing records will actually be nullified since it doesn’t exist, so when you select both shared IDs from each table, “a.user_id” will still contain the number. It’s a pretty nifty query that has saved me time iterating through values and looping through with a script. This is a good example to reference in the future.

About Author:

Senior Cloud Software Engineer and 25+ years experienced video production, video editing and 3D animation services for a variety of global clients including local video production here in Jacksonville, Florida.

Leave a Comment

Your email address will not be published. Required fields are marked *