🔥 Just Launched! Werra Premium Template for HyväSee it in Action

SQL Query to Find Missing Records between Two Related Tables

By Jignesh ParmarUpdated on May 26, 2025 2 min read

Big data requires a proper skill set to be able to make meaning out of it. SQL has become a very important tool in a data specialist’s toolbox since it is critical for accessing, updating, inserting, manipulating, and modifying data. While working with a database, you may require to run direct SQL query in Magento 2.

Furthermore, Magento 2 SQL Query for Managing Case-Sensitive Data holds significance in this context. This feature ensures precise handling of data sensitivity to letter casing, facilitating accurate data manipulation and retrieval within the Magento 2 database environment. While working with tables in the database, you may want to compare records between two related tables and find the missing data.

Today, I’ve come up with the SQL query to find missing records between two related tables.

SQL Query to Find Missing Records between Two Related Tables

Let us take a practical example of two database tables.

  1. eav_attribute_option table
  2. eav_attribute_option_swatch table

eav_attribute_option table that has a primary key on option_id and reference with the eav_attribute_option swatch table.

option_idattribute_idsort_order
1200
21281
3202

eav_attribute_option_swatch table

swatch_idoption_idstore_id
110
220
330
440
550

Here, these two tables have references to each other. Option_id 4 and 5 will not be found in the eav_attribute_option table.

In the above example, we just have a small amount of data, but when it comes to a real-life scenario, databases have a large amount of data and the necessity often arrives to find missing data from related tables.

While migrating the Magento store from Magento 1 to Magento 2, you may observe that some data is missing from the database. Hence, to solve this, the given solution helps you find the missing records between two related tables.

However, the below query is a savior.

SELECT eav_attribute_option_swatch.*
FROM eav_attribute_option_swatch
LEFT OUTER JOIN eav_attribute_option
ON eav_attribute_option_swatch.option_id = eav_attribute_option.option_id
WHERE eav_attribute_option.option_id IS NULL

Here, eav_attribute_option_swatch and eav_attribute_option  are the default databases of Magento 2.

That’s all!

Please consider sharing this post with Magento Community via social media.

Thank You

Jignesh Parmar Full Image
Article byJignesh Parmar

An expert in his field, Jignesh is the team leader at Meetanshi and a certified Magento developer. His passion for Magento has inspired others in the team too. Apart from work, he is a cricket lover.