SQL Tips

How to Compare Two Tables in SQL Server Using T-SQL

SQL Server Tips  ·  4 min read

Sometimes you need a quick T-SQL query to compare data in two tables. Whether you're validating a migration, debugging a sync issue, or checking for data drift, these statements cover the most common scenarios.

Find Rows in Source That Don't Exist in Target

Using NOT EXISTS:

SELECT * FROM t1
WHERE NOT EXISTS (
  SELECT * FROM t2 WHERE t2.Id = t1.Id
)

Using a LEFT JOIN:

SELECT t1.*
FROM t1
LEFT OUTER JOIN t2 ON t1.Id = t2.Id
WHERE t2.Id IS NULL

Using EXCEPT (SQL Server 2005+)

The EXCEPT operator is the cleanest approach when both tables have the same structure:

-- Rows in t1 not in t2
SELECT Id, Col1 FROM t1
EXCEPT
SELECT Id, Col1 FROM t2

-- Rows in t2 not in t1
SELECT Id, Col1 FROM t2
EXCEPT
SELECT Id, Col1 FROM t1

Composite Primary Keys

If your primary key spans multiple columns, adjust the WHERE clause accordingly:

SELECT Id, Col1 FROM t1
WHERE NOT EXISTS (
  SELECT 1 FROM t2
  WHERE t1.Id = t2.Id
  AND t1.Col1 = t2.Col1
)

A Note on Performance

For large tables, EXCEPT and NOT EXISTS generally perform better than LEFT JOIN ... WHERE NULL. For very large datasets, consider adding appropriate indexes on the join columns before running the comparison.

Of course, if you need to compare entire databases rather than individual tables — including schema objects, stored procedures, and multiple tables at once — that's exactly what SQL Server Comparison Tool is designed for.