To check row level differences between two records from two different tables in MySQL, where you want to see which fields have changed, follow these steps:
Using JOIN with CASE to Identify Row Level Differences in MySQL
You can compare each column individually to check row level difference and mark which ones have changed using this MySQL query.
SELECT
a.id,
CASE WHEN a.column1 = b.column1 THEN 'No Change' ELSE 'Changed' END AS column1_diff,
CASE WHEN a.column2 = b.column2 THEN 'No Change' ELSE 'Changed' END AS column2_diff,
CASE WHEN a.column3 = b.column3 THEN 'No Change' ELSE 'Changed' END AS column3_diff
FROM table1 a
JOIN table2 b ON a.id = b.id;You can add as many columns as you want.
What this does:
- Compares each field individually.
- Marks
"Changed"if different, otherwise"No Change".
Want to learn more about MySQL? Get MySQL book from https://amzn.to/45JXmH0
Dynamic Query for Large Tables
If you have many columns and don’t want to manually compare each, you can generate a query dynamically using MySQL Information Schema:
SELECT CONCAT(
'SELECT id, ',
GROUP_CONCAT(
'CASE WHEN t1.', COLUMN_NAME, ' <> t2.', COLUMN_NAME,
' THEN "', COLUMN_NAME, ' changed" ELSE "No Change" END AS ', COLUMN_NAME SEPARATOR ', '
),
' FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id'
) AS query_text
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table1' AND COLUMN_NAME != 'id';This will generate a query that automatically checks differences for all columns. Execute the generated query and you will get the differences, where all cells are marked with "Changed" or "No Change".
Conclusion
These queries can help you identify difference between two tables cell by cell. It can be useful in many ways and reduce your so much time to identify a small difference in large data.
Do you stuck with any such problem? You can write me in the comments.
Disclaimer: This post contains affiliate links. If you use these links to buy something, I may earn a commission at no extra cost to you.
