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:

SELECT column_lists from table_name1
EXCEPT
SELECT column_lists from table_name2;

NOTE: It is to note that MySQL does not support EXCEPT clause. So here we are going to use the PostgreSQL database to explain SQL EXCEPT examples.

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

CREATE TABLE public.customer
(
    id integer NOT NULL,
    name text COLLATE pg_catalog."default" NOT NULL,
    age integer NOT NULL,
    salary real NOT NULL
)

Table: Orders

CREATE TABLE public."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

INSERT INTO public.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

INSERT INTO public.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:

SELECT  id, name, amount, date
   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:

SELECT  id, name, amount, date
   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:

SELECT id, name, age, salary FROM customer
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.

Next TopicChange datatype of column in SQL

← prev next →