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.
- Read more about EXCEPT operator at Microsoft Docs.