PostgreSQL INTERSECT Operator

The INTERSECT operator in PostgreSQL is used to return the common rows that appear in the result sets of two SELECT queries. It essentially performs an "intersection" between two sets, returning only the rows that are present in both result sets.

1. Syntax of `INTERSECT`

SELECT columns
FROM table1
INTERSECT
SELECT columns
FROM table2;

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

2. Example: Basic Use of `INTERSECT`

Scenario:

Let’s assume we have two tables: employees and contractors. We want to find the people who are both employees and contractors.

`employees` Table:

employee_id	name
1	Alice
2	Bob
3	Charlie
4	David

`contractors` Table:

contractor_id	name
2	Bob
4	David
5	Edward

Query: Find People Who Are Both Employees and Contractors

SELECT name
FROM employees
INTERSECT
SELECT name
FROM contractors;

Result:

name
Bob
David

Explanation: The query returns the names of people who appear in both the employees and contractors tables. In this case, Bob and David are both employees and contractors.

3. Example: Using `INTERSECT` with Multiple Columns

You can also use INTERSECT with multiple columns to find rows that are common across multiple columns in two tables.

`orders` Table:

order_id	customer_id	order_date
1	101	2025-01-01
2	102	2025-01-02
3	103	2025-01-03

`returns` Table:

return_id	customer_id	return_date
1	101	2025-01-05
2	102	2025-01-06
3	103	2025-01-07

Query: Find Customers Who Have Placed Orders and Made Returns

SELECT customer_id, order_date
FROM orders
INTERSECT
SELECT customer_id, return_date
FROM returns;

Result:

customer_id	order_date
101	2025-01-05
102	2025-01-06
103	2025-01-07

Explanation: The query returns the customers who placed orders and made returns. The intersection is based on both the customer_id and the dates.

4. `INTERSECT` vs `IN`

While both INTERSECT and IN can be used to find common data, they are used in different contexts. INTERSECT compares the entire result set (rows) from both queries, while IN is used to filter results in a WHERE clause based on values from a subquery.

Example using `IN`:

SELECT name
FROM employees
WHERE name IN (SELECT name FROM contractors);

Difference: INTERSECT will return the exact rows from both queries, while IN simply checks if a value is present in the result of a subquery.

5. Using `INTERSECT` with Duplicates

By default, INTERSECT removes duplicate rows from the result set. If you want to retain duplicates in the result set, you can use the INTERSECT ALL operator.

Example with `INTERSECT ALL`:

SELECT name
FROM employees
INTERSECT ALL
SELECT name
FROM contractors;

Explanation: INTERSECT ALL will retain duplicates in the result set. If there are multiple occurrences of a row in both queries, they will be included in the result.

6. Important Points About `INTERSECT`

Column Matching: Both SELECT statements must return the same number of columns with compatible data types.
Duplicates: INTERSECT removes duplicates from the result set. If you need to retain duplicates, use INTERSECT ALL.
Performance: INTERSECT generally performs better than using IN for set operations.

7. Summary of `INTERSECT`

Operator	Description
`INTERSECT`	Returns the rows that are common in both queries. Duplicates are removed.
`INTERSECT ALL`	Similar to `INTERSECT`, but keeps duplicates from both queries.
Column Matching	Both queries must return the same number of columns with compatible data types.
Performance	Efficient for set-based operations, better than using `IN` in some cases.

Would you like to explore more about INTERSECT or any other SQL operators?

Souy Soeng

PostgreSQL INTERSECT Operator