SQL | Intersect & Except clause
Last Updated :
30 Jun, 2022
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 Table 2 containing details of employees who are provided bonus 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 : 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 Table 2 containing details of employees who are provided bonus 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 :
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...