SQL INTERSECT Explained: Find Shared Rows Easily

Comparing data across tables is a common requirement in SQL development. Whether validating user data or reconciling records between systems, identifying shared rows efficiently can save time and reduce complexity.

The INTERSECT operator provides a simple way to return only the rows common to two query results. It focuses on overlap without introducing additional data.

This guide covers how INTERSECT works, presents practical examples, and answers common questions about its usage.

Example Scenarios for SQL INTERSECT

Finding Common Job Titles

Use the following query to discover job roles shared between male and female employees:

SELECT title
FROM HR
WHERE Gender = 'M'
INTERSECT
SELECT title
FROM HR
WHERE Gender = 'F';

This highlights titles present in both groups.

Comparing Sales Data Across Tables

To identify matching sales records across two sources:

SELECT SaleID, Product, Amount, SaleDate
FROM Sales_2023_SourceA
INTERSECT
SELECT SaleID, Product, Amount, SaleDate
FROM Sales_2023_SourceB;

This approach extracts only those records identical in both systems, helping with audits and synchronization.

FAQ

What is SQL INTERSECT?

It retrieves rows present in both SELECT queries, filtering results to their intersection.

Are there prerequisites?

Yes. Queries must have the same number of columns, and data types must be compatible.

Can I compare more than two datasets?

Yes. Chain multiple INTERSECT operations for additional datasets.

Which DBMSs support it?

PostgreSQL, Oracle, and SQL Server fully support INTERSECT. MySQL requires alternative methods.

Conclusion

The SQL INTERSECT operator is a clean and efficient way to find shared rows between datasets.

For more in-depth explanations and advanced use cases, check out the full guide SQL INTERSECT: Everything You Need to Know.