SQL Except With Examples [Latest]

SQL Except

The Except clause is used to return all rows in the first SELECT statement that is not returned by the second SELECT statement. Both the SELECT statements will return two different datasets. The EXCEPT operator will retrieve all the result set from the first SELECT query and will remove the duplicates from the second SELECT query.

Rules for Usage of SQL Except

• The columns that you wish to compare between two SELECT statements need not have to be same fields but the corresponding columns should have the same data type.
• There must be the same number of expressions in both SELECT statements.
• The corresponding columns in each of the SELECT statements must have similar data types.
• The EXCEPT operator returns all records from the first SELECT statement that are not in the second SELECT statement.
• The EXCEPT operator in SQL Server is equivalent to the MINUS operator in Oracle.
• 

SQL Except Syntax

SELECT column_name[s] from table1
EXCEPT
SELECT column_name[s] from table2;

MySQL does not support SQL Except clause. I am using PostgreSQL database in this tutorial to show SQL Except examples.

SQL Except Example

Let us consider the following two tables for SQL Except

Customer Table

Supplier Table

Here is the script for the creation of tables and insertion of sample data in the PostgreSQL database.

CREATE TABLE public."Customer"
(
    "Customer_Id" bigint NOT NULL,
    "Customer_Name" character varying(50) COLLATE pg_catalog."default" NOT NULL,
    "State" character varying(20) COLLATE pg_catalog."default" NOT NULL,
    "Country" character varying(20) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "Customer_pkey" PRIMARY KEY ("Customer_Id")
)
CREATE TABLE public."Supplier"
(
    "Supplier_Id" bigint NOT NULL,
    "Supplier_Name" character varying(50) COLLATE pg_catalog."default" NOT NULL,
    "State" character varying(20) COLLATE pg_catalog."default" NOT NULL,
    "Country" character varying(20) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "Supplier_pkey" PRIMARY KEY ("Supplier_Id")
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;
INSERT INTO public."Customer"("Customer_Id", "Customer_Name", "State", "Country")VALUES (1, 'Akash', 'Delhi', 'India'),
	(2, 'Amit', 'Hyderabad', 'India'),
	(3, 'Jason', 'California', 'USA'),
	(4, 'John', 'Texas', 'USA'),
        (5,'Simon','London','UK');
INSERT INTO public."Supplier"("Supplier_Id", "Supplier_Name", "State", "Country")VALUES (1, 'Apple', 'California', 'USA'),
	(2, 'TCS', 'Hyderabad', 'India'),
	(3, 'Information System', 'Delhi', 'India'),
	(4, 'Solar Energy', 'Bangalore', 'India');

Let’s look into some example for SQL Except using these tables.

1. 1. SQL Except

Select "State" ,"Country" from "Customer"
Except
Select "State","Country" from "Supplier";

Output:

1. 1. SQL Except with Order By

Select "State" ,"Country" from "Customer"
Except
Select "State","Country" from "Supplier" order by "State"

Output:

In the above query, the result set is sorted based on the State Column

1. 1. SQL Except using Country Column

Select "State" ,"Country" from "Customer"
Except
Select "State","Country" from "Supplier" where "Country" = 'India'

Output:

In the above query, the first SELECT query gets all the rows and from the second SELECT statement it gets the rows where the country is India and corresponding rows are removed from the first SELECT query.