SQL Server EXCEPT Operator

Structured Query Language also known as SQL is a tool for storing, managing, and manipulating relational databases. SQL Server is a popular relational database management system (RDBMS) developed by Microsoft, providing a variety of operators to perform different operations on given datasets. One such operator is the EXCEPT operator, which allows us to retrieve distinct rows from the result set of two queries. In this article, we will see the usage of the EXCEPT operator, explore its syntax, required prerequisites, and some practical examples.

Before we see the EXCEPT operator, we need some basic understanding of SQL and relational databases. You can refer to this article to get a basic understanding of the Structured Query Language.

EXCEPT Operator

EXCEPT keyword is an operator that is used to retrieve distinct rows from the left query result set that are not present in the right query result set in a given query. It can be useful in finding the difference between two sets of data. The syntax for the EXCEPT operator is given as follows

Syntax:

SELECT column1, column2, …

FROM table1

EXCEPT

SELECT column1, column2, …

FROM table2;

The result set of the query will include all distinct rows from the first query (left) that do not appear in the second query(right).

To get better understanding of the concept we will see an example of the working of EXCEPT operator.

Examples of EXCEPT Operator

-- Customers table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50) NOT NULL
);

-- PreferredCustomers table
CREATE TABLE PreferredCustomers (
    PreferredCustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50) NOT NULL
);

-- Insert sample data
INSERT INTO Customers VALUES (1, 'Alice');
INSERT INTO Customers VALUES (2, 'Bob');
INSERT INTO Customers VALUES (3, 'Charlie');

INSERT INTO PreferredCustomers VALUES (1, 'Alice');
INSERT INTO PreferredCustomers VALUES (2, 'Bob');

The created two tables will look like given below.

CustomerID	CustomerName
1	Alice
2	Bob
3	Charlie

PreferredCustomerID	CustomerName
1	Alice
2	Bob

Now the task is two find the customers who are not Preferred Customers. To be more specific we need customers in the first table except the customers in the second table. It can be done using the following query.

SELECT CustomerID, CustomerName
FROM Customers
EXCEPT
SELECT PreferredCustomerID, CustomerName
FROM PreferredCustomers;

Output:

Output

As we can see Charlie is not present in second table that’s why when we check for customers who are in the first table but not present in second table we get the result as Charlie.

Now we will use EXCEPT operator with some of another operators.

EXCEPT with IN Operator

We can extend the functionality by combining EXCEPT with IN operator to filter results based on a specific set of values .

SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerID NOT IN (SELECT PreferredCustomerID FROM PreferredCustomers);

Output:

Output

EXCEPT with LIKE Operator

we can utilize the LIKE operator to perform pattern matching, enabling more flexiblility for comparisons comparisons.

SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerName NOT LIKE 'B%';

Output:

Output

EXCEPT with ORDER BY Clause

We can enhance the result presentation by using the ORDER BY clause to arrange output based on specific columns.

SELECT CustomerID, CustomerName
FROM Customers
EXCEPT
SELECT PreferredCustomerID, CustomerName
FROM PreferredCustomers
ORDER BY CustomerID;

Output:

Output

EXCEPT Statements in a Single Table

We can apply the EXCEPT operator within single table to identify distinct rows based on specific criteria.

SELECT EmployeeID, FirstName, LastName
FROM Employees
EXCEPT
SELECT ManagerID, FirstName, LastName
FROM Employees
WHERE ManagerID IS NOT NULL;

Output:

EmployeeID | FirstName | LastName
-----------|-----------|----------
[Output depends on the specific data in the Employees table]

Conclusion

The EXCEPT operator in SQL Server provides a powerful way to identify and extract unique records from given one dataset that are not present in another dataset. By facilitating the comparison of data across various tables, this operator gives power to database professionals to pinpoint and address discrepancies efficiently. By providing a concise syntax the EXCEPT Operator enables users to identify distinct rows in the left query result set that do not have corresponding matches in the right query result set from the given database tables. Using the EXCEPT operator in your SQL toolkit can enhance your ability to manage relational databases.