SQL | Intersect & Except clause

In SQL, INTERSECT and EXCEPT are set operations used to compare results from two SELECT statements. INTERSECT returns only the rows that are common to both queries. EXCEPT returns the rows that are in the first query but not in the second.

INTERSECT Clause

The INTERSECT clause returns only the rows that are common to two SELECT queries. It combines results from both queries and keeps only the matching rows.

Syntax:

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

INTERSECT

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

In the above syntax:

• Retrieves selected columns (column-1, column-2…) from two tables.
• INTERSECT returns only the rows that are common to both SELECT statements.
• The WHERE clauses filter data in each query before finding the intersection

Now let's understand this with the help of 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;
 

Output:

EXCEPT Clause

The EXCEPT clause returns rows that are in the first SELECT query but not in the second. It is used to find data present in one set and missing in another.

Syntax:

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

EXCEPT

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

In the above syntax

• Retrieves selected columns (column-1, column-2…) from two tables.
• EXCEPT returns rows from the first query that are not in the second query.
• The WHERE clauses filter data in each query before performing the comparison.

Now let's understand this 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;

Output:

Note: These clauses are useful for analyzing data across multiple tables or queries efficiently.