How to Compare Two Tables in SQL and Find Differences

Comparing two tables to identify discrepancies is a common task in SQL development. Whether you’re tracking data changes, validating backups, or synchronizing information, knowing how to pinpoint differences efficiently is crucial. This article explores various techniques for comparing tables in SQL and highlights their strengths and weaknesses, focusing on the EXCEPT and LEFT JOIN methods.

Building Sample Tables for Comparison

Before diving into comparison techniques, let’s create two sample tables, SourceTable and DestinationTable, with slight differences in their data. This will serve as our testing ground for illustrating the methods.

USE [master];
GO
IF DATABASEPROPERTYEX('SqlHabits', 'Version') IS NOT NULL
BEGIN
    ALTER DATABASE SqlHabits SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE SqlHabits;
END;
GO
CREATE DATABASE SqlHabits;
GO
USE SqlHabits;
GO

CREATE TABLE dbo.SourceTable (
    Id INT NOT NULL,
    FirstName NVARCHAR(250) NOT NULL,
    LastName NVARCHAR(250) NOT NULL,
    Email NVARCHAR(250) NULL
);
GO

CREATE TABLE dbo.DestinationTable (
    Id INT NOT NULL,
    FirstName NVARCHAR(250) NOT NULL,
    LastName NVARCHAR(250) NOT NULL,
    Email NVARCHAR(250) NULL
);
GO

INSERT INTO dbo.SourceTable (Id, FirstName, LastName, Email)
VALUES
(1, 'Chip', 'Munk', '[email protected]'),
(2, 'Frank', 'Enstein', '[email protected]'),
(3, 'Penny', 'Wise', '[email protected]');
GO

INSERT INTO dbo.DestinationTable (Id, FirstName, LastName, Email)
VALUES
(1, 'Chip', 'Munk', '[email protected]'),
(2, 'Frank', 'Ensein', '[email protected]'),  -- Spelling error in LastName
(3, 'Penny', 'Wise', NULL); -- Email is NULL
GO

We’ve intentionally introduced discrepancies in the DestinationTable: a spelling error in the LastName for ID 2 and a NULL value for Email in ID 3.

Comparing Tables with LEFT JOIN

A common approach to compare tables is using a LEFT JOIN. This retrieves all rows from the left table (SourceTable) and matching rows from the right table (DestinationTable). Non-matching rows from the right table result in NULL values. To highlight differences, we use a WHERE clause to filter rows where columns don’t match.

SELECT st.Id, st.FirstName, st.LastName, st.Email
FROM dbo.SourceTable st
LEFT JOIN dbo.DestinationTable dt ON dt.Id = st.Id
WHERE dt.FirstName <> st.FirstName
    OR dt.LastName <> st.LastName
    OR ISNULL(dt.Email, '') <> ISNULL(st.Email, '');
GO

The ISNULL function handles potential NULL values, ensuring accurate comparison. However, this approach becomes cumbersome with numerous columns, requiring extensive OR conditions and ISNULL checks for each column.

Comparing Tables with EXCEPT

The EXCEPT set operator provides a more concise way to identify differences. It returns rows from the first SELECT statement that don’t exist in the second SELECT statement. This eliminates the need for explicit NULL checks, simplifying the query significantly.

SELECT Id, FirstName, LastName, Email
FROM dbo.SourceTable
EXCEPT
SELECT Id, FirstName, LastName, Email
FROM dbo.DestinationTable;
GO

This query efficiently returns rows from SourceTable that differ from DestinationTable, highlighting the discrepancies without verbose NULL handling.

Considerations When Using EXCEPT

While EXCEPT offers conciseness, it’s crucial to be aware of its limitations. Performance-wise, LEFT JOIN often outperforms EXCEPT, especially with large datasets. EXCEPT also requires an equal number of columns in both SELECT statements.

Conclusion

Choosing between LEFT JOIN and EXCEPT depends on your specific needs. LEFT JOIN offers potentially better performance and flexibility, while EXCEPT provides a cleaner syntax for simple comparisons. Understanding both techniques empowers you to choose the most efficient method for comparing tables in SQL and finding differences.