INTERSECT for common rows in SQL

Introduction

INTERSECT helps find rows that appear in both tables or queries. It shows only the common data.

You want to find customers who bought products from two different stores.

You want to see which employees are listed in both the sales and support departments.

You want to find common items in two different inventory lists.

You want to compare two lists of students to find those enrolled in both classes.

Syntax

SQL

SELECT column_list FROM table1
INTERSECT
SELECT column_list FROM table2;

Both SELECT statements must have the same number of columns and compatible data types.

INTERSECT returns only distinct rows that appear in both queries.

Examples

Finds employee names present in both years.

SQL

SELECT name FROM employees_2023
INTERSECT
SELECT name FROM employees_2024;

Finds products sold in both stores.

SQL

SELECT product_id, product_name FROM store_a
INTERSECT
SELECT product_id, product_name FROM store_b;

Finds cities where customers exist in both regions.

SQL

SELECT city FROM customers_north
INTERSECT
SELECT city FROM customers_south;

Sample Program

This example finds fruit names that are in both fruits_a and fruits_b tables.

SQL

CREATE TABLE fruits_a (name VARCHAR(20));
CREATE TABLE fruits_b (name VARCHAR(20));

INSERT INTO fruits_a VALUES ('Apple'), ('Banana'), ('Cherry');
INSERT INTO fruits_b VALUES ('Banana'), ('Cherry'), ('Date');

SELECT name FROM fruits_a
INTERSECT
SELECT name FROM fruits_b;

Important Notes

INTERSECT removes duplicates automatically; no need to use DISTINCT.

If no rows are common, INTERSECT returns an empty result.

Column order and data types must match in both SELECT statements.

Summary

INTERSECT shows only rows common to both queries.

Use it to compare lists and find shared data.

Both queries must have matching columns and types.