Mysql: Compare differences between two tables

sql

join

union-all

information-schema

byAlex Kataev·Dec 1, 2024

To identify differences between two MySQL tables, the LEFT JOIN and UNION can be utilized to show non-matching or unique entries. Focus on a common identifier to contrast between tables, then single out non-overlapping or discrepant records with a straightforward query:

SELECT a.*, 'table1_only' AS source FROM table1 a
LEFT JOIN table2 b USING (id)
WHERE b.id IS NULL 
-- Like my ex, we're trying to find all the things that 'don't belong' in table1.

UNION ALL

SELECT b.*, 'table2_only' AS source FROM table2 b
LEFT JOIN table1 a USING (id)
WHERE a.id IS NULL;
-- Similar to above, but this time we're ghosting 'all the things that don't belong' in table2.

This concise piece of SQL fetches rows exclusive to table1 and table2 using their unique id parameter. Replace id with the actual unique key that your tables employ.

Extended analysis using multiple columns

Multiple columns can be compared across tables t1 and t2 by implementing the ROW function and combining it with UNION ALL and NOT IN. This method provides a comprehensive analysis, much like a Swiss army knife for discrepancies:

SELECT * FROM (
  SELECT ROW(t1.col1, t1.col2, t1.col3) as row_data FROM t1
  -- Let's get all row data from table t1 and store it as a single row. It's time for spring cleaning!

  UNION ALL

  SELECT ROW(t2.col1, t2.col2, t2.col3) FROM t2
  -- Similar to above, we're now collecting the row data from table t2.
) as combined_table
GROUP BY row_data
HAVING COUNT(*) = 1;
-- Using GROUP BY and HAVING, we ensure only unique row data surface like a crispy potato chip in a sea of standard-issue potatoes. Yum!

Encountering database tool constraints

Some database tools may impose their restrictions. For example, the free variant of DbVisualizer. Adopt cohesive error handling practices with your SQL management tools:

SELECT * FROM t1
WHERE NOT EXISTS (
  SELECT 1 FROM t2
  WHERE t2.id = t1.id
)
AND t1.some_column IS NOT NULL;
-- Essentially we are playing "peek-a-boo" with our data here. If we don't see it, it doesn't exist... right?

Scripting can boost ability to visualize differences

PHP and mysqli can greatly enhance your toolbox when SQL alone calls for reinforcement. Compare databases and visualize results with ease:

<?php
    $connection = mysqli_connect('host', 'user', 'password', 'database');
    if (!$connection) {
        die('Connection failed: ' . mysqli_connect_error());
    }
    // Congrats! You've either successfully connected or experienced the sweet release of calling die().

    $query = 'Your SQL query here';
    $result = mysqli_query($connection, $query);
    // You can process and visualize the $result using your favorite method here, let your creativity shine!
?>

Exhaustive JOIN outcomes

Left join can do wonders. Illuminate rows that don't match the other table:

SELECT t1.*, t2.* 
FROM t1
LEFT JOIN t2 
ON t1.id = t2.id AND t1.col1 = t2.col1 AND t1.col2 = t2.col2
WHERE t2.id IS NULL;
-- This is like a high school reunion, we're looking for old acquaintances who've noticeably changed.

Automating comparison strategy

Leverage INFORMATION_SCHEMA.TABLES for efficiency when dealing with various tables or dynamic schemas:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'YourDatabaseName';
-- This query can be your cheat sheet to all the gossip, or rather, metadata about your tables.