AppDividend
Latest Code Tutorials

SQL Except Clause Example | Except Statement In SQL

1

SQL EXCEPT operator is used to combine two select statements, which in turn returns the row that is present in the first select statement and not in the second select statement. This Clause acts like a subtract operation that we perform in sets and Venn diagrams.

SQL Except Clause Example

SQL EXCEPT returns those tuples that are returned by the first SELECT operation, and not returned by the second SELECT operation. Let’s understand from the below diagram.

#Pictorial Representation

SQL Except Clause

From above you can see that no common records will be displayed between two queries i.e., only Query1 will return all its rows.

 Each Select statement within an EXCEPT query must have the same number of fields in the result sets having similar data types.

#Syntax

Select column(s) from table_name1 where(condition)
EXCEPT
Select column(s) from table_name2 where(condition).

#Parameters

  1. Column(s) represents the name of the columns.
  2. Table_name1 and Table_name2 are the names of the tables.
  3. Where(condition) is the condition to be imposed on select statements.
  4. EXCEPT is the operator who acts like a minus operator on select statements.

Let’s clear the above operations with examples.

Consider Tables: (Students) and (Course)

STUDENTS:

RollNameCity
1RohitPatna
2ShouvikJalandhar
3ShubhKolkata
4KaranAllahabad
5ShivamPalampur
6KaranDhulian

 

Course:

NameCourse
ShubhData Science
RohitData Science
ShivamAndroid Development

 

#QUERY

Select name from Students
Except
Select name from Course;

#OUTPUT

NAME
Shouvik
Karan

 

#EXPLANATION

In the above query name of students were declared who have not registered for any of the courses. So, Shouvik and Karan were displayed in the resulting set because they had not logged in any of the courses.

NOTE:

You can see that in the above query Karan was displayed only one time. So, to retain duplicates, we have to use EXCEPTALL clause.

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.

See the following query.

Select name from Students
EXCEPTALL
Select name from Course

#OUTPUT

NAME
Shouvik
Karan
Karan

 

So, you can see that Karan has been displayed twice.

#DIFFERENCE BETWEEN EXCEPT AND NOT IN CLAUSE

Except clause removes duplicates from the resulting set whereas NOT IN clause does not remove duplicates from the resulting set.

NOTE:

MySQL does not support EXCEPT CLAUSE. So, it is better to use it in SQL SERVER.

Finally, SQL Except Clause Example is over.

1 Comment
  1. Rikimaru says

    Hi Ankit Lathiya,

    I would like to ask if i got 2 same table with same structure and key (2 Keys) how do I find the unmatched record with except statement with where clause
    For exemple:
    Table 1 – Key field (ID NO. & NAME)
    ID NO. |NAME |COUNTRY
    10001 |ALI |MALAYSIA
    10002 |MUTU |INDIA
    10003 |AHHUA|CHINA

    Table 2 – Key field (ID NO. & NAME)
    ID NO. |NAME |COUNTRY
    10001 |ALI |MALAYSIA
    10002 |MUTU |INDIA
    10003 |AHHUA|SINGAPORE

    Expected Result
    ID NO. |NAME |COUNTRY
    10003 |AHHUA|SINGAPORE

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.