SQL Server - Using EXCEPT Operator

* Compatibility SQL Server 2005 onwards
EXCEPT operator was introduced in SQL Server 2005. EXCEPT can be used to return distinct records from the left side of a query and which are not located on the right side.

EXCEPT operator has the following behavior:

• EXCEPT by default returns a DISTINCT result set

• EXCEPT requires same column number and order on both side of the query with comparable data type

For more details on EXCEPT visit: MSDN

Let us consider a real-world EXCEPT example using NorthWind database. You can download and install NorthWind database from the following location.

(a) Customers who never placed any Order (Using EXCEPT)

SELECT
CustomerID
FROM
Customers
EXCEPT
SELECT
CustomerID
FROM
Orders

CustomerID
--------------
FISSA
PARIS
….
--------------

Query returns 2 rows, which are unique CustomerID. The above query demonstrated that we can extract unique CustomerID for all the Customers who never placed an Order using EXCEPT operator instead of writing a LEFT OUTER JOIN syntax to accomplish the same output.

(b) Customers who never placed any Order (Using LEFT OUTER JOIN)

SELECT
DISTINCT C.CustomerID
FROM
Customers C
LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID
WHERE
O.CustomerID IS NULL

CustomerID
--------------
FISSA
PARIS
….

--------------
Query returns same output as (a) using LEFT OUTER JOIN syntax