TSQL – Easy way to find rows that do not exist in another table using the EXCEPT operator

There are times you have two tables that look the same and seem to have the same rows in each. Using the EXCEPT operator, you can find any rows that do not exist in either table.

Below are two tables that are named similarly, but do not look to have the same amount of rows. It would be nice to see what rows are missing from each.

The SQL EXCEPT clause/operator is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. This means EXCEPT returns only rows, which are not available in the second SELECT statement.

In the example below, we are checking which rows in TMP_AGING1 do not exist in the TMP_AGING2 table. There are 6 rows.

Now if we flip the query around, we can see what rows in TMP_AGING2 do not exist in TMP_AGING1. There is just 1 row.

To read more about the EXCEPT clause, please follow this link to the documentation. https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-ver16