SQL Except With Examples

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

 

SQL Except Representation

SQL Except Syntax

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.

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

    1. SQL Except

Output:

STATE COUNTRY
Texas USA
London UK
Except

 

SQL Except

    1. SQL Except with Order By

Output:

STATE COUNTRY
London UK
Texas USA
SQL_Except_OrderBy

 

SQL Except Using Order By Clause

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

    1. SQL Except using Country Column

Output:

STATE COUNTRY
London UK
California USA
Texas USA
SQL_Except_By_Country

 

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.

By admin

Leave a Reply