INTERSECT is a set operator that returns only the rows that appear in BOTH result sets of two SELECT statements.

Key points:

  • Works like a set intersection (common rows)
  • Automatically removes duplicates

Both queries must return:

  • Same number of columns
  • Compatible data types
  • Same column order

Step 1: Basic Syntax

SELECT column_list
FROM table1INTERSECTSELECT column_list FROM table2;

Step 2: Example tables

Employees_2024

EmployeeID Name Department
1 John IT
2 Jane HR
3 Mike IT

Employees_2025

EmployeeID Name Department
2 Jane HR
3 Mike IT
4 Sara Finance

Step 3: Simple INTERSECT example

Find employees who appear in both years:

SELECT EmployeeID, Name, Department
FROM Employees_2024INTERSECTSELECT EmployeeID, Name, Department
FROM Employees_2025;

Result:

EmployeeID Name Department
2 Jane HR
3 Mike IT

Only rows that exist in both tables are returned.

Step 4: INTERSECT removes duplicates

Even if a row appears multiple times, INTERSECT returns it once.

SELECT Name
FROM Employees_2024INTERSECTSELECT Name FROM Employees_2025;

Step 5: INTERSECT with WHERE clause

Each SELECT can have its own filter:

SELECT Name
FROM Employees_2024
WHERE Department = 'IT'INTERSECTSELECT Name
FROM Employees_2025
WHERE Department = 'IT';

Result:

Name
Mike

Step 6: INTERSECT with ORDER BY

ORDER BY must be placed at the end:

SELECT EmployeeID, Name
FROM Employees_2024INTERSECTSELECT EmployeeID, Name
FROM Employees_2025
ORDER BY Name;

Categorized in:

SQL Server,