SQL Server EXCEPT Operator
Last Updated : 04 Jan, 2024
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.
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:
OutputAs 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:
OutputEXCEPT 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:
OutputEXCEPT 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:
OutputEXCEPT 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.
Similar Reads
SQL Server INTERSECT Operator
In SQL Server, the INTERSECT operator is a kind of set operator that is used to combine the results of two SELECT statements and return rows which is common between them. In this article, We will explore the syntax, key concepts, and practical examples of using the INTERSECT operator. Whether you ar
5 min read
SQL Server ALL Operator
In SQL Server the logical operators are used to perform conditional checks in SQL Queries. There are many different types of Logical operators like ANY, AND, LIKE, EXISTS, NOT, IN, and BETWEEN in SQL Server. These operators are very useful to filter and compare single or multiple data in SQL Queries
3 min read
SQL Server ANY Operator
In SQL Server there are many different types of Logical operators like ANY, AND, LIKE, EXISTS, NOT, IN, and BETWEEN. The logical operators are used to perform conditional checks in SQL Queries. These operators are very useful to filter and compare single or multiple data in SQL Queries. In this arti
3 min read
SQLite Except Operator
SQLite is a server-less database engine written in C programming language. It is developed by D. Richard Hipp in the year 2000. The main motive for developing SQLite is to escape from complex database engines like MySQL etc. It has become one of the most popular database engines as we use it in Tele
5 min read
SQL Server INTERSECT and EXCEPT Operator
Multiple SQL Queries may return duplicate values in recordsets from each query result. There could be situations when a single resultset is needed to be returned by combining multiple SQL queries. In SQL Server, there are many SET operators like UNION, EXCEPT, and INTERSECT to merge multiple results
4 min read
SQL Server Arithmetic Operators
Microsoft's SQL Server, a capable relational database management system, has a lot of features to deal with data and make manipulations purely. SQL Server, in addition to the many features available to users, has a set of operators for arithmetic which allows users of databases to perform mathematic
3 min read
PostgreSQL - EXISTS Operator
The EXISTS operator in PostgreSQL is a powerful SQL feature used to check the existence of rows in a subquery. It is particularly useful when working with correlated subqueries, where the inner query depends on values from the outer query. The EXISTS operator returns true if the subquery returns at
4 min read
Arithmetic Operators in SQL Server
Arithmetic operators play a crucial role in performing mathematical calculations within SQL Server. These operators allow you to perform addition, subtraction, multiplication, division, and more on numeric data stored in your database tables. In this article, we'll explore the various arithmetic ope
4 min read
PL/SQL EXISTS Operator
The EXISTS operator in PL/SQL is a powerful tool used to check the existence of records in a subquery. Unlike traditional comparison operators that evaluate data values, EXISTS focuses on whether a set of conditions returns any rows. It is commonly used to determine the presence or absence of record
6 min read
SQL OR Operator
The SQL OR operator is a powerful tool used to filter records in a database by combining multiple conditions in the WHERE clause. When using OR, a record will be returned if any of the conditions connected by the operator are true. This allows for more flexibility in querying and is important when w
7 min read