SQL EXCEPT
Usually, we use a JOIN clause to get the combined result from more than one table. Sometimes, we need a result set that contains records from one table but not available in the other table. In that case, SQL provides an EXCEPT clause/operator.
The EXCEPT clause in SQL is widely used to filter records from more than one table. This statement first combines the two SELECT statements and returns records from the first SELECT query, which aren't present in the second SELECT query's result. In other words, it retrieves all rows from the first SELECT query while deleting redundant rows from the second.
This statement behaves the same as the minus operator does in mathematics. This article will illustrate how to use the SQL EXCEPT clause with the help of basic examples.
Rules for SQL EXCEPT
We should consider the following rules before using the EXCEPT statement in SQL:
- In all SELECT statements, the number of columns and orders in the tables must be the same.
- The corresponding column's data types should be either the same or compatible.
- The fields in the respective columns of two SELECT statements cannot be the same.
SQL EXCEPT Syntax
The following syntax illustrates the use of EXCEPT clause:
EXCEPT
SELECT column_lists from table_name2;
The below image explains the working of EXCEPT operation in the two tables T1 and T2:
Illustration:
- Table T1 includes data 1, 2, and 3.
- Table T2 includes data 2, 3, and 4.
When we execute the EXCEPT query on these tables, we will get 1, which is unique data from the T1, and it will not found in the T2.
SQL EXCEPT Example
Let us first create two tables using the following scripts:
Table: Customer
(
id integer NOT NULL,
name text COLLATE pg_catalog."default" NOT NULL,
age integer NOT NULL,
salary real NOT NULL
)
Table: Orders
(
order_id integer NOT NULL,
date date NOT NULL,
cust_id integer NOT NULL,
amount real NOT NULL,
CONSTRAINT "order_pkey" PRIMARY KEY ("order_id")
)
Next, we will insert some records into both tables as follows:
Table: customer
id, name, age, salary)
VALUES (101, 'John', 24, 20000)
(102, 'Mike', 22, 25000),
(103, 'Emily', 24, 22000),
(104, 'James', 20, 30000),
(105, 'Sophia', 21, 35000);
Table: orders
order_id, date, cust_id, amount)
VALUES (1, '2009-10-08', 103, 1500),
(2, '2009-11-06', 103, 1000),
(3, '2009-12-05', 102, 2500),
(4, '2009-09-08', 101, 1800);
Next, we will use a SELECT statement to verify the records. See the below image:
Let's look into an example for SQL EXCEPT using these tables. Suppose we want to join these tables in our SELECT statement as shown below:
FROM customer
LEFT JOIN orders
ON customer.id = orders. order_id
EXCEPT
SELECT id, name, amount, date
FROM customer
RIGHT JOIN orders
ON customer.id = orders. order_id;
It will produce the below output:
EXCEPT with ORDER BY Clause
If we want to sort the result set obtained by the EXCEPT operator, we need to add the ORDER BY clause in the query. For example, the following example joins both tables and sorts the result set by their name in ascending order:
FROM customer
LEFT JOIN orders
ON customer.id = orders. order_id
EXCEPT
SELECT id, name, amount, date
FROM customer
RIGHT JOIN orders
ON customer.id = orders. order_id
ORDER BY name;
It will produce the below output:
EXCEPT statements in a single table
Generally, we use the EXCEPT statements in two tables, but we can also use them to filter records from a single table. For example, the following EXCEPT statement will return all the records from the customer table where the age is greater than 21:
EXCEPT
SELECT id, name, age, salary FROM customer WHERE age > 21;
In this script, the first SELECT query returns all records from the customer table, and the second query all those records whose age is greater than 21. Next, the EXCEPT statement filters the records using both SELECT statements and returns only those rows whose age is greater than 21.
How is EXCEPT different from NOT IN Clause?
EXCEPT is different from the NOT IN clause in the following manner:
- EXCEPT clause removes all duplicates in the result, set automatically whereas NOT IN does not remove duplicate records.
- EXCEPT clause can perform comparison in single or multiple columns. Whereas the NOT IN clause can perform comparison in a single column only.
