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:
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.
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.
Contact Us