Skip to content
Related Articles

Related Articles

SQL | Intersect & Except clause

View Discussion
Improve Article
Save Article
  • Difficulty Level : Basic
  • Last Updated : 30 Jun, 2022
View Discussion
Improve Article
Save Article

1. INTERSECT clause : As the name suggests, the intersect clause is used to provide the result of the intersection of two select statements. This implies the result contains all the rows which are common to both the SELECT statements. Syntax :

SELECT column-1, column-2 …… 
FROM table 1
WHERE…..

INTERSECT

SELECT column-1, column-2 …… 
FROM table 2
WHERE…..

Example : Table 1 containing Employee Details table1 Table 2 containing details of employees who are provided bonus table2 Query :

SELECT ID, Name, Bonus 
FROM
table1 
LEFT JOIN
table2
ON table1.ID = table2.Employee_ID

INTERSECT

SELECT ID, Name, Bonus 
FROM
table1 
RIGHT JOIN
table2
ON table1.ID = table2.Employee_ID;
 

Result : table3   2. EXCEPT clause :  contains all the rows that are returned by the first SELECT operation, and not returned by the second SELECT operation.  Syntax :

SELECT column-1, column-2 …… 
FROM table 1
WHERE…..

EXCEPT

SELECT column-1, column-2 …… 
FROM table 2
WHERE…..

Example : Table 1 containing Employee Details table1 Table 2 containing details of employees who are provided bonus table2 Query :

SELECT ID, Name, Bonus 
FROM
table1 
LEFT JOIN
table2
ON table1.ID = table2.Employee_ID

EXCEPT

SELECT ID, Name, Bonus 
FROM
table1 
RIGHT JOIN
table2
ON table1.ID = table2.Employee_ID;

Result : table4

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!