How to find non-matching records from 2 tables in SQL Server?
This post will demonstrate how to select all records from two tables except those records that are in common.
We have table orders1:
| id |
|---|
| 123 |
| 234 |
| 345 |
| 456 |
| 567 |
| 678 |
And table orders2:
| id |
|---|
| 123 |
| 234 |
| 456 |
| 567 |
| 789 |
| 890 |
Now we need to select all records from the table orders1 that do not appear in orders2, and all records from table orders2 that are not in table orders1.
This query can solve the problem:
(
SELECT id FROM orders1
EXCEPT
SELECT id FROM orders2
)
UNION
(
SELECT id FROM orders2
EXCEPT
SELECT id FROM orders1
)
The result is:
| id |
|---|
| 345 |
| 678 |
| 789 |
| 890 |
You can also use this query as an alternative:
SELECT id
FROM (
SELECT DISTINCT id FROM orders1
UNION ALL
SELECT DISTINCT id FROM orders2
) AS temp_tbl
GROUP BY id HAVING COUNT(*) = 1
If you only want to see the records from table orders1 but not the orders2, use this query instead:
SELECT
orders1.id
FROM
orders1
WHERE
NOT EXISTS
(
SELECT * FROM orders2
WHERE orders2.id = orders1.id
)
The result is:
| id |
|---|
| 345 |
| 678 |
Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS
