🔥 Working with Intersect and Except in SQL

When dealing with datasets in SQL, you often need to find common records in two tables or determine the differences between them. For these purposes, SQL provides two useful operators: INTERSECT and EXCEPT. Let’s take a closer look at how they work.

🔻 The INTERSECT Operator
The INTERSECT operator is used to find rows that are present in both queries. It works like the intersection of sets in mathematics, returning only those records that exist in both datasets.

Example:

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

This will return rows that appear in both table1 and table2.

Key Points:
- The INTERSECT operator automatically removes duplicate rows from the result.
- The selected columns must have compatible data types.

🔻 The EXCEPT Operator
The EXCEPT operator is used to find rows that are present in the first query but not in the second. This is similar to the difference between sets, returning only those records that exist in the first dataset but are missing from the second.

Example:

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

Here, the result will include rows that are in table1 but not in table2.

Key Points:
- The EXCEPT operator also removes duplicate rows from the result.
- As with INTERSECT, the columns must have compatible data types.

📊 What’s the Difference Between UNION, INTERSECT, and EXCEPT?
- UNION combines all rows from both queries, excluding duplicates.
- INTERSECT returns only the rows present in both queries.
- EXCEPT returns rows from the first query that are not found in the second.

📌 Real-Life Examples
1. Finding common customers. Use INTERSECT to identify customers who have made purchases both online and in physical stores.
2. Determining unique products. Use EXCEPT to find products that are sold in one store but not in another.

By using INTERSECT and EXCEPT, you can simplify data analysis and work more flexibly with sets, making it easier to solve tasks related to finding intersections and differences between datasets.

Happy querying!