Checking your session…
Module 11: Aggregation, Set Operators & Subqueries

11.4 INTERSECT / EXCEPT

Level: 11 | Version: v1.1 | Author: Meptrasoft

Overview

INTERSECT and EXCEPT compare the results of two SELECT queries.

Operator Purpose
INTERSECT Returns rows present in both results
EXCEPT Returns rows from the first result that are not in the second

INTERSECT → Common rows EXCEPT → First result only

INTERSECT vs EXCEPT — Set Operations on Query Results
Figure 1: INTERSECT returns common rows, while EXCEPT returns rows present in the first result but not the second.

Practice Tables

This topic reuses the employees and contractors tables from 11.3.

INTERSECT

INTERSECT returns rows that appear in both query results.

SQL 
SELECT name, department, salary
FROM employees

INTERSECT

SELECT name, department, salary
FROM contractors;

The result contains rows that match in all selected columns.

INTERSECT → Common rows

EXCEPT

EXCEPT returns rows from the first SELECT result that do not appear in the second.

SQL 
SELECT name, department, salary
FROM employees

EXCEPT

SELECT name, department, salary
FROM contractors;

With the original practice data, this returns no rows because every employee row also appears in contractors.

EXCEPT → First result minus second result

Simple Comparison

TEXT 
INTERSECT
A ∩ B
→ COMMON ROWS

EXCEPT
A − B
→ FIRST RESULT ONLY

For example:

TEXT 
A = Alice, Bob, Carol
B = Alice, Carol, David

INTERSECT → Alice, Carol
EXCEPT → Bob

Important Rule

As with UNION, the two SELECT statements must return the same number of columns, with compatible data types in corresponding positions.

SQL 
SELECT name, salary FROM employees

INTERSECT

SELECT name, salary FROM contractors;

Common Beginner Mistakes

✗ Mistake ✓ Correct Understanding
INTERSECT returns all rows from both queries It returns only common rows
EXCEPT compares tables permanently It compares query result sets
EXCEPT returns rows unique to both sides It returns rows from the first result that are absent from the second
The SELECTs can have different columns They must have the same number of compatible columns
Column order does not matter Corresponding columns must be compatible

Placement Quick Points

TEXT 
INTERSECT
→ COMMON RESULTS

EXCEPT
→ FIRST RESULT ONLY
  • INTERSECT returns common rows.
  • EXCEPT returns rows from the first result that are not in the second.
  • Both operate on SELECT result sets.
  • Corresponding queries must return the same number of compatible columns.
  • These operators are useful for comparing datasets.

Interview Questions

What is INTERSECT?

INTERSECT returns rows that exist in both SELECT results.

What is EXCEPT?

EXCEPT returns rows from the first SELECT result that do not exist in the second.

What is the difference?
TEXT 
INTERSECT → Common rows
EXCEPT     → First result only
What requirement applies to both queries?

They must return the same number of columns with compatible data types in corresponding positions.

How can EXCEPT be useful?

It can help identify records that exist in one dataset but not another.

Practice & Hands-On Exercises

Using employees and contractors:

  1. Find rows common to both tables using INTERSECT:
SQL 
SELECT name, department, salary FROM employees
INTERSECT
SELECT name, department, salary FROM contractors;
  1. Find employee rows that are not present in contractors using EXCEPT:
SQL 
SELECT name, department, salary FROM employees
EXCEPT
SELECT name, department, salary FROM contractors;
  1. Explain the difference between INTERSECT and EXCEPT.
  2. Identify how these operators can help compare two datasets.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

TEXT 
INTERSECT → COMMON
EXCEPT     → FIRST ONLY

INTERSECT finds common rows between two query results, while EXCEPT finds rows that exist only in the first result.

End of INTERSECT / EXCEPT