SQL | Intersect & Except clause

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 :
This works exactly opposite to the INTERSECT clause. The result, in this case, contains all the rows except the common rows of the two SELECT statements.

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

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


3


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.