Get records from one table which are not present in another table using EXCEPT

There are several ways to get records from one table which are not present in another table. Some technique includes the traditional JOIN clause, sub query, NOT IN, NOT EXISTS, etc… But, here we will see a simple way by using EXCEPT operator.

In SQL Server, EXCEPT operator will return the records from the first select statement which are not present in the second select statement. Let us see how it works. For this illustration, I’m using the tables Sales.Customers and Sales.Orders in the WideWorldImporters database. Here I have to list down all the CustomerID who does not placed any orders during a specific year. Below is the sample SQL Statement.

SELECT CustomerID
	FROM Sales.Customers
EXCEPT
SELECT CustomerID
	FROM Sales.Orders
	WHERE YEAR(OrderDate) = 2015;

What’s Next? Read about INTERSECT operator.

Reference

• Read more about EXCEPT operator at Microsoft Docs.