Finding Rows That Differ Between Two Tables With EXCEPT

Two tables can have the same row count and still contain different data. EXCEPT provides a direct way to find distinct rows present on one side and absent from the other.

Create a Small Comparison Case

The sample uses two temporary tables with the same ordered column definitions. They represent independent snapshots, so the same key can hold different values. One key exists only on each side, one shared key changes, and another shared key contains NULL in both copies. These are deliberate inputs for explaining the comparison.

CREATE TABLE #LeftRows
(
    ItemID int NOT NULL PRIMARY KEY,
    ItemName nvarchar(40) NOT NULL,
    Quantity int NULL
);
CREATE TABLE #RightRows
(
    ItemID int NOT NULL PRIMARY KEY,
    ItemName nvarchar(40) NOT NULL,
    Quantity int NULL
);
INSERT #LeftRows VALUES (1,N'Cable',5),(2,N'Adapter',NULL),(3,N'Bracket',2);
INSERT #RightRows VALUES (1,N'Cable',8),(2,N'Adapter',NULL),(4,N'Clip',1);

I use explicit column lists for comparisons even when the tables look identical. SELECT star makes column position depend on schema history. A later added column can change the meaning of the report or make it fail. Write the attributes that belong to the comparison contract and keep their order identical.

Compare Both Directions With EXCEPT

The left-minus-right expression returns rows from the first input that do not match a complete selected row in the second. It does not inspect the tables' names for a preferred source. Reversing the inputs is necessary to reveal the other unmatched population. Keep the direction labels visible when saving the results.

SELECT ItemID, ItemName, Quantity FROM #LeftRows
EXCEPT
SELECT ItemID, ItemName, Quantity FROM #RightRows;
SELECT ItemID, ItemName, Quantity FROM #RightRows
EXCEPT
SELECT ItemID, ItemName, Quantity FROM #LeftRows;

The changed cable row appears in both directional reports, with its respective values. The bracket and clip appear only from their originating sides. A one-direction comparison therefore cannot establish complete equality. It can answer a deliberately narrower question, such as whether every proposed source row already exists in the target.

The selected columns need compatible, comparable types. Character comparisons follow applicable collation rules. Normalize deliberately when databases use different collations, and decide whether case and accents should distinguish values. Do not hide a meaningful case difference just to make a comparison finish successfully.

Find the Shared Rows

INTERSECT identifies distinct selected rows that appear on both sides. It complements the difference reports by showing the accepted matching population. Match means the entire selected row agrees under the comparison rules, rather than only the key. This is useful for reconciliation evidence and for checking a deliberately chosen subset.

SELECT ItemID, ItemName, Quantity FROM #LeftRows
INTERSECT
SELECT ItemID, ItemName, Quantity FROM #RightRows;

A shared key with different quantity values does not belong to this matching result when quantity is included. If the report selects only ItemID, that same key does match. State the selected attributes beside the results, because a short matching-key list cannot prove that all corresponding data values are equal.

Understand NULL Equality in Set Comparison

For the distinct-row comparison used by these set operators, two NULL values in corresponding positions are treated as equal. The adapter row therefore belongs to the matching population in this example. This differs from using ordinary equality between two nullable expressions, where NULL equals NULL does not evaluate to true.

That distinction is central to using EXCEPT for reconciliation. You do not need to replace every NULL with a made-up sentinel solely to make the set operation compare two missing values. Sentinel substitutions can collapse a real value and a missing value into the same representation. Keep the actual domain and NULL behavior visible.

When building a join-based difference report instead, write explicit NULL-aware comparison or use a version-supported distinctness predicate. Mixing ordinary inequality into a nullable comparison can silently omit changed rows. A tidy-looking result set is not proof that the predicate tested every intended case.

Narrow the EXCEPT Comparison to Relevant Attributes

Choose columns according to the business question. An audit timestamp or rowversion differs naturally after separate writes and can overwhelm a report intended to compare business values. Conversely, leaving out quantity would conceal the cable change in this sample. There is no universally correct SELECT list independent of the comparison's purpose.

SELECT ItemID, ItemName FROM #LeftRows
EXCEPT
SELECT ItemID, ItemName FROM #RightRows;

The narrowed report asks about keys and names only. It does not answer whether quantities agree. Keep excluded attributes documented so a reader does not infer broader equivalence. If a column's value needs normalization, preserve the original and report the transformation rule alongside the normalized comparison.

Label the Combined EXCEPT Differences

Wrap each directional operation before adding its label. If the label is included inside the compared SELECT lists, opposite labels make otherwise identical rows look different. The wrapper lets the data comparison finish first and then attaches provenance to the resulting unmatched rows.

SELECT N'Left' AS SourceSide, d.ItemID, d.ItemName, d.Quantity
FROM
(
    SELECT ItemID, ItemName, Quantity FROM #LeftRows
    EXCEPT
    SELECT ItemID, ItemName, Quantity FROM #RightRows
) AS d
UNION ALL
SELECT N'Right' AS SourceSide, d.ItemID, d.ItemName, d.Quantity
FROM
(
    SELECT ItemID, ItemName, Quantity FROM #RightRows
    EXCEPT
    SELECT ItemID, ItemName, Quantity FROM #LeftRows
) AS d;

Use UNION ALL here to retain both labeled populations directly. The internal set operations already produce distinct compared rows. Parentheses and derived tables also make evaluation boundaries readable when the report combines several operations. A reconciliation query should reveal its intended grouping without requiring a precedence puzzle.

Separate Added Keys From Changed Values

A key-based FULL OUTER JOIN answers a different question: which identities are missing, and which shared identities have changed attributes? The temporary tables' primary keys make this one-to-one. Without a unique key, duplicate matches can multiply join rows and distort the report.

SELECT COALESCE(l.ItemID,r.ItemID) AS ItemID,
       CASE WHEN l.ItemID IS NULL THEN 'Only right'
            WHEN r.ItemID IS NULL THEN 'Only left'
            ELSE 'Changed' END AS Difference,
       l.Quantity AS LeftQuantity, r.Quantity AS RightQuantity
FROM #LeftRows AS l FULL OUTER JOIN #RightRows AS r
  ON r.ItemID = l.ItemID
WHERE l.ItemID IS NULL OR r.ItemID IS NULL
   OR l.ItemName <> r.ItemName
   OR l.Quantity <> r.Quantity
   OR (l.Quantity IS NULL AND r.Quantity IS NOT NULL)
   OR (l.Quantity IS NOT NULL AND r.Quantity IS NULL);

I use this report when the reviewer needs a proposed action per key, rather than two whole-row differences. Keep the action decision separate from the query. Only left can mean an intended deletion, a missing load, or a retention difference; the direction alone does not authorize an INSERT or DELETE.

Compare Stable Snapshots and Duplicate Counts

Which point in time do the two tables represent? If either changes during collection, differences can reflect capture timing rather than a defective load. Use an approved consistent snapshot method and record the boundary. Do not hold a long production transaction merely because it makes a report convenient.

Use EXCEPT for distinct-row differences and keep a separate multiplicity check when repeated rows matter. The set operations remove duplicate selected rows. If duplicate multiplicity matters, group by the compared attributes and compare COUNT_BIG values separately. Also review actual execution plans and resource use for large inputs. Exact comparison is valuable, but repeated full-table reconciliation still needs an appropriate schedule and scope. Keep the report precise enough that a quiet result means what you intended it to mean.

Related reading on this blog: SQL Server: Find Distinct Result Sets Using EXCEPT Operator and NOT IN With a NULL in the List Returns No Rows.

A row difference is not an automatic correction, it is a comparison result whose meaning depends on keys and selected attributes.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.