When it comes to working with SQL Server, there are many powerful features and functionalities that can greatly enhance your database management and development skills. However, it’s surprising to see that even after many years since its release, some SQL Server operators are still relatively unknown to many professionals in the field.
One such operator is the EXCEPT operator, which was introduced in SQL Server 2005. In this article, we will explore the EXCEPT operator and compare it to another commonly used operator, NOT IN, to understand their differences and similarities.
The Difference Between EXCEPT and NOT IN
The EXCEPT operator returns all of the distinct rows from the query to the left of the EXCEPT operator when there are no matching rows in the right query. Essentially, it performs a left anti semi join. On the other hand, the NOT IN operator also returns distinct values from the left query that are not present in the right query.
Both EXCEPT and NOT IN achieve similar results and have the same execution plan. However, the EXCEPT operator offers a simpler syntax, making it easier to use and understand.
Example Usage
Let’s take a look at an example to better understand how the EXCEPT operator works:
USE AdventureWorks;
GO
SELECT ProductID
FROM Production.Product
EXCEPT
SELECT ProductID
FROM Production.WorkOrder;
GO
In this example, we are selecting all the distinct ProductIDs from the Production.Product table, except for those that are present in the Production.WorkOrder table. The result will be a list of ProductIDs that are unique to the Production.Product table.
Now, let’s compare this with the equivalent query using the NOT IN operator:
USE AdventureWorks;
GO
SELECT ProductID
FROM Production.Product
WHERE ProductID NOT IN (
SELECT ProductID
FROM Production.WorkOrder);
GO
As you can see, both queries return the same number of rows and have the same execution plan. However, the EXCEPT operator provides a more concise and readable syntax.
Conclusion
The EXCEPT operator in SQL Server is a powerful tool that allows you to easily compare and retrieve distinct values from one query that are not present in another query. While it may not be as well-known as other operators, such as NOT IN, it offers a simpler and more intuitive syntax.
By familiarizing yourself with the EXCEPT operator and incorporating it into your SQL Server queries, you can enhance your database management and development skills, and ultimately improve the efficiency and effectiveness of your work.