SQL Except

Filed Under: SQL

We usually join two tables in order to get a combined result set. But, there are cases when we want a result set which is available only in one table and not available in the other table. SQL provides a feature called Except. Exception literally means not included. SQL except is also very similar to the same concept.

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 diagram

SQL Except Representation

 

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

CUSTOMER ID CUSTOMER NAME STATE COUNTRY
1 Akash Delhi India
2 Amit Hyderabad India
3 Jason California USA
4 John Texas USA
5 Simon London UK

Supplier Table

SUPPLIER ID SUPPLIER NAME STATE COUNTRY
1 Apple California USA
2 TCS Hyderabad India
3 Information System Delhi India
4 Solar Energy Bangalore India

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. SQL Except
  2. Select "State" ,"Country" from "Customer"
    Except
    Select "State","Country" from "Supplier";
    

    Output:

    STATE COUNTRY
    Texas USA
    London UK
    sql except example

    SQL Except

  3. SQL Except with Order By
  4. Select "State" ,"Country" from "Customer"
    Except
    Select "State","Country" from "Supplier" order by "State"
    

    Output:

    STATE COUNTRY
    London UK
    Texas USA
    sql except order by

    SQL Except Using Order By Clause

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

  5. SQL Except using Country Column
  6. Select "State" ,"Country" from "Customer"
    Except
    Select "State","Country" from "Supplier" where "Country" = 'India'
    

    Output:

    STATE COUNTRY
    London UK
    California USA
    Texas USA
    sql except with where clause

    SQL Except using Where Clause

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages