Javatpoint Logo
Javatpoint Logo

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:

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:

SQL EXCEPT

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

Table: Orders

Next, we will insert some records into both tables as follows:

Table: customer

Table: orders

Next, we will use a SELECT statement to verify the records. See the below image:

SQL EXCEPT

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:

It will produce the below output:

SQL EXCEPT

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:

It will produce the below output:

SQL EXCEPT

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:

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 TopicSQL MCQ




Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA