SQL – EXCEPT

Prev     Next

SQL EXCEPT clause is also same as SQL INTERSECT. But, EXCEPT clause returns all records from the 1st SELECT statement, except those are found in the 2nd SELECT statement.

SQL Syntax for EXCEPT:

SQL Syntax for EXCEPT

SELECT column1, column2, etc FROM table1 EXCEPT SELECT column1, column2, etc FROM table2;

Please consider following 2 tables with few records as given below.

Table1:

Table name (for example): student1
Column names in this table: Student_ID, Student_name, City and Age
Available records: 4 rows

Table2:

Table name (for example): student2
Column names in this table: Student_ID, Department, College and Rank
Available records: 4 rows

Example for how to use SQL EXCEPT:

As described earlier, EXCEPT will return all records from 1^st SQL except which are found in 2^nd SQL as mentioned in below output.

SQL query:

SELECT Student_ID, City FROM student1
EXCEPT
SELECT Student_ID, City FROM student2;

Description:

In above SQL EXCEPT, 2 SELECT SQL’s are executed, and then EXCEPT verifies the result set of both SELECT queries and returns all records from 1st SELECT SQL except the records returned from 2nd SELECT SQL.

SQL query Output:

• Output of SQL statement – SELECT Student_ID, City FROM student1;

• Output of SQL statement – SELECT Student_ID, City FROM student2;

• Final Output using EXCEPT: (Total records – 1)

Prev     Next