Comparing 2 Tables in SQL: A Comprehensive Guide to Finding Differences

Structured Query Language (SQL) is the backbone of modern data management, essential for creating, maintaining, and retrieving data from relational databases like MySQL, PostgreSQL, SQL Server, and Oracle. A common task for database administrators and developers is comparing two tables within a database to identify discrepancies, track changes, or ensure data consistency. This article delves into effective techniques for Comparing 2 Tables In Sql, providing you with practical examples and methods to pinpoint differences efficiently.

Whether you’re auditing data, migrating databases, or simply ensuring data integrity, understanding how to compare tables is a crucial skill. Let’s explore various SQL methods to achieve this.

Setting Up Our Sample Database

To illustrate the comparison techniques, let’s create a sample database named “company” and populate it with two tables: department_old and department_new. These tables will represent snapshots of department information at different points in time, allowing us to simulate a real-world scenario where data changes over time.

First, we’ll create the database:

CREATE DATABASE company;

Next, we’ll use this database for our operations:

USE company;

Defining the department_old Table

This table represents the initial state of our department data. We’ll define it with columns for ID, SALARY, NAME, and DEPT_ID:

CREATE TABLE department_old (
    ID INT,
    SALARY INT,
    NAME VARCHAR(20),
    DEPT_ID VARCHAR(255)
);

Now, let’s populate department_old with some sample data:

INSERT INTO department_old VALUES
    (1, 34000, 'ANURAG', 'UI DEVELOPERS'),
    (2, 33000, 'HARSH', 'BACKEND DEVELOPERS'),
    (3, 36000, 'SUMIT', 'BACKEND DEVELOPERS'),
    (4, 36000, 'RUHI', 'UI DEVELOPERS'),
    (5, 37000, 'KAE', 'UI DEVELOPERS');

To verify the data, we can use a simple SELECT statement:

SELECT * FROM department_old;

ID	SALARY	NAME	DEPT_ID
1	34000	ANURAG	UI DEVELOPERS
2	33000	HARSH	BACKEND DEVELOPERS
3	36000	SUMIT	BACKEND DEVELOPERS
4	36000	RUHI	UI DEVELOPERS
5	37000	KAE	UI DEVELOPERS

This table represents our original department data.

Defining the department_new Table

The department_new table represents an updated version of the department data. We’ll use the same structure as department_old:

CREATE TABLE department_new (
    ID INT,
    SALARY INT,
    NAME VARCHAR(20),
    DEPT_ID VARCHAR(255)
);

Let’s populate department_new with data, including some changes and additions compared to department_old:

INSERT INTO department_new VALUES
    (1, 34000, 'ANURAG', 'UI DEVELOPERS'),
    (2, 33000, 'HARSH', 'BACKEND DEVELOPERS'),
    (3, 36000, 'SUMIT', 'BACKEND DEVELOPERS'),
    (4, 36000, 'RUHI', 'UI DEVELOPERS'),
    (5, 37000, 'KAE', 'UI DEVELOPERS'),
    (6, 37000, 'REHA', 'BACKEND DEVELOPERS');

Verifying the data in department_new:

SELECT * FROM department_new;

ID	SALARY	NAME	DEPT_ID
1	34000	ANURAG	UI DEVELOPERS
2	33000	HARSH	BACKEND DEVELOPERS
3	36000	SUMIT	BACKEND DEVELOPERS
4	36000	RUHI	UI DEVELOPERS
5	37000	KAE	UI DEVELOPERS
6	37000	REHA	BACKEND DEVELOPERS

*Output of `SELECT FROM department_new` in SQL Server Management Studio:**

Output of SELECT * FROM department_new showing table data

Now that we have our sample tables set up, let’s explore different SQL techniques for comparing them.

Method 1: Using UNION ALL and GROUP BY to Identify Differences

This method is effective for finding rows that are present in one table but not the other, or rows that are different across the two tables. The core idea is to combine all rows from both tables using UNION ALL, and then use GROUP BY and HAVING to identify rows that appear only once.

Logic:

1. UNION ALL: Combines all rows from department_old and department_new into a single result set. If a row is identical in both tables, it will appear twice in the combined result.
2. GROUP BY: Groups the combined rows based on all columns we want to compare (in this case, ID, SALARY, NAME, DEPT_ID).
3. *`HAVING COUNT() = 1**: Filters the grouped rows, keeping only those groups that have a count of 1. This means these rows appeared only once in the combined result set, indicating they are unique to eitherdepartment_oldordepartment_new` or different between them.

SQL Query:

SELECT ID, SALARY, NAME, DEPT_ID
FROM (
    SELECT ID, SALARY, NAME, DEPT_ID FROM department_old
    UNION ALL
    SELECT ID, SALARY, NAME, DEPT_ID FROM department_new
) AS CombinedDepartments
GROUP BY ID, SALARY, NAME, DEPT_ID
HAVING COUNT(*) = 1;

Explanation of the Query:

• The inner SELECT statement with UNION ALL creates a combined dataset from both tables.
• The outer SELECT statement then groups these combined rows by all columns (ID, SALARY, NAME, DEPT_ID).
• Finally, HAVING COUNT(*) = 1 filters out groups that appear more than once, thus isolating the rows that are different or unique to one of the tables.

Output:

Executing this query against our department_old and department_new tables will return the rows that are different. In our example, it will show the row with ID = 6 (‘REHA’, ‘BACKEND DEVELOPERS’) because it exists only in department_new.

If there are no differences between the tables based on the compared columns, this query will return an empty result set.

Method 2: Using EXCEPT (or MINUS) to Find Rows in One Table But Not the Other

The EXCEPT operator (or MINUS in some SQL dialects like Oracle) provides a more direct way to find rows that exist in the first table but not in the second.

Logic:

EXCEPT compares the result sets of two SELECT statements and returns only the rows from the first SELECT statement that are not present in the result set of the second SELECT statement.

SQL Query to find rows in department_old but not in department_new:

SELECT ID, SALARY, NAME, DEPT_ID FROM department_old
EXCEPT
SELECT ID, SALARY, NAME, DEPT_ID FROM department_new;

Output:

For our example, this query will return an empty result set because all rows in department_old (based on ID, SALARY, NAME, DEPT_ID) are also present in department_new.

SQL Query to find rows in department_new but not in department_old:

SELECT ID, SALARY, NAME, DEPT_ID FROM department_new
EXCEPT
SELECT ID, SALARY, NAME, DEPT_ID FROM department_old;

Output:

This query will return the row that is unique to department_new:

ID	SALARY	NAME	DEPT_ID
6	37000	REHA	BACKEND DEVELOPERS

EXCEPT is a concise and efficient way to identify rows that are missing from one table compared to another.

Method 3: Using LEFT JOIN to Highlight Differences

LEFT JOIN can also be used to compare tables, especially when you want to see all rows from one table and identify matches (or mismatches) in the other.

Logic:

1. LEFT JOIN from department_old to department_new (or vice versa) based on a common key (e.g., ID).
2. Check for NULL values in the columns of the right table (department_new in this case) in the WHERE clause. NULL values indicate that there is no matching row in department_new for a row in department_old.

SQL Query to find rows in department_old that are not in department_new (based on ID):

SELECT d_old.*
FROM department_old d_old
LEFT JOIN department_new d_new ON d_old.ID = d_new.ID
WHERE d_new.ID IS NULL;

Output:

This query will return an empty set, similar to the first EXCEPT example, as there are no IDs in department_old that are missing in department_new in our example.

SQL Query to find rows in department_new that are not in department_old (based on ID):

SELECT d_new.*
FROM department_new d_new
LEFT JOIN department_old d_old ON d_new.ID = d_old.ID
WHERE d_old.ID IS NULL;

Output:

This query will return the row from department_new with ID = 6 because there’s no matching ID in department_old:

ID	SALARY	NAME	DEPT_ID
6	37000	REHA	BACKEND DEVELOPERS

Using LEFT JOIN to compare specific column differences:

You can also extend LEFT JOIN to compare specific columns and identify rows where values differ in corresponding columns. For example, to find rows where the SALARY is different for the same ID in both tables:

SELECT d_old.*, d_new.*
FROM department_old d_old
LEFT JOIN department_new d_new ON d_old.ID = d_new.ID
WHERE d_old.SALARY <> d_new.SALARY
  OR (d_new.ID IS NULL AND d_old.ID IS NOT NULL); -- Handle cases where ID exists in old but not new if needed

This query would highlight rows where the salary has changed between the old and new tables.

Choosing the Right Method

The best method for comparing 2 tables in SQL depends on your specific needs:

• UNION ALL and GROUP BY: Effective for finding any rows that are different across all compared columns, regardless of which table they originate from. Good for a general overview of differences.
• EXCEPT: Best for quickly identifying rows that are present in one table but entirely absent from the other. Simple and efficient for set difference operations.
• LEFT JOIN: Most flexible, allowing you to compare tables based on specific join conditions (like ID) and to pinpoint differences in specific columns. Useful for detailed comparisons and identifying changes in particular attributes.

In conclusion, SQL provides powerful tools for comparing 2 tables. By understanding these techniques – UNION ALL, EXCEPT, and LEFT JOIN – you can effectively analyze data differences, maintain data integrity, and perform various data auditing and migration tasks. Choose the method that best suits your comparison goals and database system capabilities.