How INTERSECT works in SQL? Best INTERSECT examples

How INTERSECT works in SQL? Best INTERSECT examples

When working with SQL, I often need to combine data from multiple queries. One of the lesser-known but powerful operators I use is INTERSECT. It allows me to find common records between two or more result sets. Let’s dive into how INTERSECT works in SQL and explore some of the best INTERSECT examples.

What is INTERSECT in SQL?

The INTERSECT operator in SQL is used to find common records between two SELECT queries. It returns only the rows that appear in both result sets. Unlike UNION, which combines unique rows from multiple queries, INTERSECT focuses on identifying shared records.

Here’s the basic syntax:


SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2;

Both queries must have the same number of columns, and each corresponding column must have the same data type.

Example: Finding Common Customers

Let’s say I have two tables:

  • Customers_Online – contains customers who purchased online
  • Customers_Store – contains customers who purchased in a physical store

I want to find customers who bought from both channels. I can use INTERSECT as follows:


SELECT customer_id, name FROM Customers_Online
INTERSECT
SELECT customer_id, name FROM Customers_Store;

This will return only those customers who made purchases both online and in the physical store.

INTERSECT vs. UNION vs. EXCEPT

SQL offers several set operators that behave differently. Let me break down how INTERSECT compares with UNION and EXCEPT:

Operator Description
INTERSECT Returns only the rows that exist in both result sets.
UNION Combines all unique rows from both result sets.
EXCEPT Returns rows that exist in the first result set but not in the second.

Using INTERSECT with Multiple Columns

INTERSECT works best when I want to match multiple columns. Let’s take an example where I need to find employees who worked in the same department and had identical salaries in two different databases.


SELECT emp_id, department, salary FROM Employees_2023
INTERSECT
SELECT emp_id, department, salary FROM Employees_2024;

This query will return employees who had the same department and salary in both years.

Common Use Cases for INTERSECT

I use INTERSECT in various situations, including:

  1. Finding students enrolled in multiple courses.
  2. Identifying products available in multiple warehouses.
  3. Retrieving employees who worked in the same department across different locations.
  4. Filtering users who are subscribed to two or more services.

Performance Considerations

While INTERSECT is useful, it does have performance implications. Since it requires comparing large data sets and removing duplicates, it can be slow with massive databases.

Here are some optimization tips I follow:

  • Ensure indexes exist on the involved columns.
  • Use EXISTS for smaller datasets if performance is a concern.
  • Avoid unnecessary columns in the SELECT query.

Conclusion

The INTERSECT operator in SQL is a great tool for finding shared records across multiple queries. It’s particularly helpful when I need to identify common data points between result sets. While not as commonly used as UNION or EXCEPT, it plays a crucial role in data analysis and reporting.

 

Other interesting article:

How UNION ALL works in SQL? Best UNION ALL examples