What is the role of the INTERSECT operator in SQL and in what scenarios is it used?

The INTERSECT operator in SQL is used to find the intersection of two data sets — that is, the rows that are present in both query results. It returns only the records that exist in both the first and second queries.

Use cases for INTERSECT:

• Finding common elements in two tables or datasets.
• Checking data overlap, such as common customers, orders, etc.

Example:

SELECT customer_id FROM orders_2023
INTERSECT
SELECT customer_id FROM orders_2024;

This query returns a list of customers who placed orders in both 2023 and 2024.