INTERSECT and EXCEPT
Find common rows with INTERSECT and compute set differences with EXCEPT, then compare these operations with JOIN and NOT EXISTS.
In this lesson, you will learn
- ✓Find overlapping records between queries using INTERSECT
- ✓Find rows present in query A but absent from query B using EXCEPT
- ✓Understand how duplicate elimination works in set operations
- ✓Meet column-count and compatible-data-type requirements
- ✓Compare set operators with equivalent JOIN and NOT EXISTS patterns
- ✓Understand how NULL values affect set-operation results
- ✓Use EXCEPT ALL and INTERSECT ALL in PostgreSQL
- ✓Apply set operations to practical reporting and data comparison tasks
- ✓Inspect query plans without assuming one syntax is always faster
1. Introduction to INTERSECT and EXCEPT
Set operations let you compare the results of SELECT statements.
You already learned:
- UNION combines rows from both query results.
- UNION ALL combines rows and preserves duplicates.
Two other useful set operations are INTERSECT and EXCEPT.
### INTERSECT
INTERSECT returns rows that appear in both query results.
Think of two lists of students. INTERSECT finds the students present in both lists, based on the columns you select.
### EXCEPT
EXCEPT returns rows from the first query that do not appear in the second query.
Think of a list of registered students and a list of students who submitted an assignment. EXCEPT can help identify registered students who have not submitted, provided both queries return comparable student identifiers.
### Visualize the operations
Imagine two sets, A and B:
- **A INTERSECT B:** the shared part of A and B.
- **A EXCEPT B:** the part of A that is outside B.
- **B EXCEPT A:** the part of B that is outside A.
EXCEPT is directional. A EXCEPT B is not generally the same as B EXCEPT A.
### General syntax
```sql
SELECT column_name
FROM first_table
INTERSECT
SELECT column_name
FROM second_table;
```
For EXCEPT:
```sql
SELECT column_name
FROM first_table
EXCEPT
SELECT column_name
FROM second_table;
```
These examples use PostgreSQL syntax.
2. Prepare Sample Tables
We will compare students enrolled in two courses.
### Create the tables
```sql
CREATE TABLE python_students (
student_id INTEGER PRIMARY KEY,
student_name VARCHAR(100) NOT NULL
);
CREATE TABLE sql_students (
student_id INTEGER PRIMARY KEY,
student_name VARCHAR(100) NOT NULL
);
```
### Insert sample data
```sql
INSERT INTO python_students (student_id, student_name)
VALUES
(1, 'Asha'),
(2, 'Rahul'),
(3, 'Neha'),
(4, 'Kabir');
INSERT INTO sql_students (student_id, student_name)
VALUES
(2, 'Rahul'),
(3, 'Neha'),
(5, 'Meera');
```
Our data is:
**Python students**
| student_id | student_name |
|---:|---|
| 1 | Asha |
| 2 | Rahul |
| 3 | Neha |
| 4 | Kabir |
**SQL students**
| student_id | student_name |
|---:|---|
| 2 | Rahul |
| 3 | Neha |
| 5 | Meera |
Rahul and Neha appear in both tables.
Asha and Kabir appear only in the Python table. Meera appears only in the SQL table.
We will use these records throughout the lesson.
3. INTERSECT: Find Common Rows
INTERSECT returns distinct rows that appear in both query results.
### Example 1: Find common student IDs
```sql
SELECT student_id
FROM python_students
INTERSECT
SELECT student_id
FROM sql_students
ORDER BY student_id;
```
Expected output:
| student_id |
|---:|
| 2 |
| 3 |
IDs 2 and 3 are present in both tables.
### Example 2: Return names and IDs
```sql
SELECT student_id, student_name
FROM python_students
INTERSECT
SELECT student_id, student_name
FROM sql_students
ORDER BY student_id;
```
Expected output:
| student_id | student_name |
|---:|---|
| 2 | Rahul |
| 3 | Neha |
### Why does selecting columns matter?
Set operations compare the complete selected rows.
Suppose you select only student_name. If both queries return the same name, that name can appear once even if the source records have different IDs.
When you select student_id and student_name, both values must match for the complete row to be shared.
**Important:** INTERSECT is not a replacement for every JOIN. INTERSECT returns the shared selected rows; JOIN can combine columns from related records.
Find students enrolled in both courses
Only complete rows present in both query results are returned. ORDER BY sorts the final result.
4. EXCEPT: Find Rows Missing from the Second Result
EXCEPT returns distinct rows from the first query that do not appear in the second query.
### Example: Students in Python but not SQL
```sql
SELECT student_id, student_name
FROM python_students
EXCEPT
SELECT student_id, student_name
FROM sql_students
ORDER BY student_id;
```
Expected output:
| student_id | student_name |
|---:|---|
| 1 | Asha |
| 4 | Kabir |
Asha and Kabir are present in the Python result but absent from the SQL result.
### Reverse the queries
```sql
SELECT student_id, student_name
FROM sql_students
EXCEPT
SELECT student_id, student_name
FROM python_students
ORDER BY student_id;
```
Expected output:
| student_id | student_name |
|---:|---|
| 5 | Meera |
The result changes because EXCEPT evaluates the first result relative to the second.
### Remember the direction
```sql
A EXCEPT B
```
means:
"Return rows from A that are not in B."
It does not mean "return all rows that are different in either direction."
To find rows that are exclusive to either side, calculate both directions and combine them with UNION ALL if that is the required behavior.
Find Python students absent from the SQL result
EXCEPT removes rows that also occur in the second result and returns the remaining distinct rows from the first result.
5. INTERSECT vs EXCEPT vs UNION
| Operator | What it returns |
|---|---|
| UNION | Distinct rows from either result |
| UNION ALL | Every row from both results |
| INTERSECT | Distinct rows shared by both results |
| EXCEPT | Distinct rows from the first result that are absent from the second |
Using the student examples:
- UNION of the IDs returns 1, 2, 3, 4, 5.
- INTERSECT of the IDs returns 2, 3.
- Python IDs EXCEPT SQL IDs returns 1, 4.
- SQL IDs EXCEPT Python IDs returns 5.
### A simple memory trick
- **UNION:** combine.
- **INTERSECT:** overlap.
- **EXCEPT:** subtract the second result from the first.
All these operators compare the selected output rows. They do not automatically compare records by a primary key unless the selected columns and query logic make that the intended comparison.
6. Requirements for Set Operations
INTERSECT and EXCEPT have the same basic result-structure requirements as UNION.
### Rule 1: Match the number of columns
Incorrect:
```sql
SELECT student_id, student_name
FROM python_students
INTERSECT
SELECT student_id
FROM sql_students;
```
The first query returns two columns, while the second returns one.
Correct:
```sql
SELECT student_id
FROM python_students
INTERSECT
SELECT student_id
FROM sql_students;
```
### Rule 2: Use compatible data types
Corresponding columns must have compatible types.
For example, in PostgreSQL:
```sql
SELECT student_id::TEXT AS identifier
FROM python_students
EXCEPT
SELECT student_id::TEXT
FROM sql_students;
```
Both queries now return text values.
### Rule 3: Column positions matter
The first column in the first SELECT is compared with the first column in the second SELECT, and so on.
Column names alone do not determine how rows match.
### Rule 4: The result normally uses the first SELECT's column names
Give the first query clear aliases if you need meaningful output column names.
### Rule 5: Use ORDER BY for final ordering
```sql
SELECT student_id
FROM python_students
INTERSECT
SELECT student_id
FROM sql_students
ORDER BY student_id;
```
Without ORDER BY, the database does not guarantee a particular row order.
### Rule 6: Duplicate handling
INTERSECT and EXCEPT remove duplicates by default. PostgreSQL also supports ALL variants, discussed later.
7. Comparing INTERSECT with JOIN
INTERSECT can identify shared values. An INNER JOIN can also identify matching records, but the result structures and duplicate behavior differ.
### Find common student IDs with INTERSECT
```sql
SELECT student_id
FROM python_students
INTERSECT
SELECT student_id
FROM sql_students;
```
### Find common student IDs with INNER JOIN
```sql
SELECT p.student_id
FROM python_students AS p
INNER JOIN sql_students AS s
ON s.student_id = p.student_id;
```
For these sample tables, both queries return IDs 2 and 3.
### What is the difference?
INTERSECT:
- Compares complete selected rows.
- Removes duplicate result rows by default.
- Does not let you add unrelated columns from the other table unless they are selected compatibly in both queries.
INNER JOIN:
- Combines columns from matching rows.
- Can return multiple rows when the join keys are not unique.
- Can expose attributes from both tables.
### Example where duplicates matter
If the tables contained repeated student IDs and no unique constraint prevented them, a JOIN could return multiple matching combinations. INTERSECT would return distinct common rows unless you used INTERSECT ALL.
Do not assume these forms are interchangeable in every query. Compare the required output and duplicate semantics before replacing one with the other.
8. Comparing EXCEPT with NOT EXISTS
EXCEPT can find rows in one result that are absent from another. NOT EXISTS can express a similar requirement using a matching condition.
### Using EXCEPT
```sql
SELECT student_id
FROM python_students
EXCEPT
SELECT student_id
FROM sql_students
ORDER BY student_id;
```
Expected IDs: 1 and 4.
### Using NOT EXISTS
```sql
SELECT p.student_id
FROM python_students AS p
WHERE NOT EXISTS (
SELECT 1
FROM sql_students AS s
WHERE s.student_id = p.student_id
)
ORDER BY p.student_id;
```
Expected IDs: 1 and 4.
### Why are the results similar here?
The student_id columns are primary keys. Each ID uniquely identifies a student in each table, so the anti-match query and the set difference identify the same IDs.
### Important difference
EXCEPT compares the complete selected rows. NOT EXISTS checks whether a matching row exists according to the condition you write.
For example, if you use EXCEPT on both student_id and student_name, the name also affects row equality. If you use NOT EXISTS with only student_id in the condition, the name does not affect whether a match exists.
Choose the columns and matching condition based on the business rule.
9. NULL Values in Set Operations
Ordinary SQL equality comparisons involving NULL produce UNKNOWN, but set operations use duplicate and row-comparison rules that treat corresponding NULL values as not distinct.
### INTERSECT example
```sql
SELECT NULL::INTEGER AS optional_code
INTERSECT
SELECT NULL::INTEGER AS optional_code;
```
Expected output:
| optional_code |
|---|
| NULL |
The NULL row appears in both results, so INTERSECT returns it.
### EXCEPT example
```sql
SELECT NULL::INTEGER AS optional_code
EXCEPT
SELECT NULL::INTEGER AS optional_code;
```
Expected output: zero rows.
The row in the first result is also present in the second result, so EXCEPT removes it.
### Compare this with NOT EXISTS
```sql
SELECT p.optional_code
FROM (
VALUES (NULL::INTEGER), (1)
) AS p(optional_code)
WHERE NOT EXISTS (
SELECT 1
FROM (
VALUES (NULL::INTEGER)
) AS s(optional_code)
WHERE s.optional_code = p.optional_code
);
```
With ordinary equality, NULL = NULL is UNKNOWN. Therefore, the NULL value from the outer input does not find a matching row and is retained by NOT EXISTS.
This can differ from EXCEPT, which treats matching NULL rows as not distinct for set-operation comparison.
**Interview tip:** When replacing EXCEPT with NOT EXISTS, pay attention to NULL handling. If null-safe equality is required in PostgreSQL, IS NOT DISTINCT FROM can be used in the matching condition.
10. INTERSECT ALL and EXCEPT ALL in PostgreSQL
PostgreSQL supports ALL variants of both operators.
The default forms eliminate duplicate rows. The ALL variants preserve multiplicity according to multiset rules.
### INTERSECT ALL
If a row appears twice in the first result and three times in the second, INTERSECT ALL returns it twice: the smaller of the two occurrence counts.
```sql
SELECT value
FROM (VALUES (1), (1), (2)) AS a(value)
INTERSECT ALL
SELECT value
FROM (VALUES (1), (1), (1), (3)) AS b(value)
ORDER BY value;
```
Expected output:
| value |
|---:|
| 1 |
| 1 |
### EXCEPT ALL
EXCEPT ALL subtracts the occurrence count in the second result from the occurrence count in the first, never returning a negative number of occurrences.
```sql
SELECT value
FROM (VALUES (1), (1), (1), (2)) AS a(value)
EXCEPT ALL
SELECT value
FROM (VALUES (1), (1), (3)) AS b(value)
ORDER BY value;
```
Expected output:
| value |
|---:|
| 1 |
| 2 |
The first result contains three occurrences of 1, and the second contains two, so one occurrence remains. The value 2 appears only in the first result.
### When are ALL variants useful?
They can be useful when duplicate counts are meaningful, such as comparing repeated events or reconciling data feeds.
Use them only when preserving occurrence counts is part of the requirement.
11. Practical Use Cases
### Use case 1: Find users in both campaigns
```sql
SELECT user_id
FROM campaign_a_users
INTERSECT
SELECT user_id
FROM campaign_b_users;
```
This identifies user IDs appearing in both campaign results.
### Use case 2: Find products absent from a promotion list
```sql
SELECT product_id
FROM all_products
EXCEPT
SELECT product_id
FROM promoted_products;
```
This returns product IDs present in the first result but absent from the second.
### Use case 3: Compare two permission lists
```sql
SELECT permission_name
FROM required_permissions
EXCEPT
SELECT permission_name
FROM assigned_permissions;
```
This identifies required permission names that are absent from the assigned list, assuming the names are the intended comparison key.
### Use case 4: Compare application data snapshots
```sql
SELECT record_id, record_status
FROM snapshot_today
EXCEPT
SELECT record_id, record_status
FROM snapshot_yesterday;
```
This returns distinct rows present in today's snapshot but not yesterday's. It does not, by itself, classify all possible changes to an entity; that depends on the selected columns and comparison requirements.
### Use case 5: Find values exclusive to either list
To find distinct IDs that appear in exactly one of two lists:
```sql
(
SELECT student_id
FROM python_students
EXCEPT
SELECT student_id
FROM sql_students
)
UNION
(
SELECT student_id
FROM sql_students
EXCEPT
SELECT student_id
FROM python_students
)
ORDER BY student_id;
```
Expected output:
| student_id |
|---:|
| 1 |
| 4 |
| 5 |
This combines the two directional differences.
12. Performance and Query Plans
Set operations may require the database to compare rows and eliminate duplicates. The execution strategy depends on the query and the optimizer.
### Inspect INTERSECT
```sql
EXPLAIN
SELECT student_id
FROM python_students
INTERSECT
SELECT student_id
FROM sql_students;
```
### Inspect EXCEPT
```sql
EXPLAIN
SELECT student_id
FROM python_students
EXCEPT
SELECT student_id
FROM sql_students;
```
### Inspect an anti-match query
```sql
EXPLAIN
SELECT p.student_id
FROM python_students AS p
WHERE NOT EXISTS (
SELECT 1
FROM sql_students AS s
WHERE s.student_id = p.student_id
);
```
### Best practices
- Select only the columns required for the comparison.
- Make sure the chosen columns represent the intended identity or matching rule.
- Use EXPLAIN to inspect the planned operations.
- Use EXPLAIN ANALYZE when actual runtime measurements are needed and executing the query is appropriate.
- Test on representative data sizes and distributions.
- Do not assume EXCEPT is always faster than NOT EXISTS, or vice versa.
- Prefer clarity and correctness before micro-optimizing.
Primary keys and indexes may help matching operations, but an index does not guarantee that the optimizer will use it for a particular set operation.
13. Guided Practice
Use the python_students and sql_students tables from this lesson.
### Exercise 1: Find common IDs
Return student IDs present in both tables using INTERSECT.
### Exercise 2: Find common complete rows
Return student_id and student_name for students whose complete rows appear in both results.
### Exercise 3: Find Python-only IDs
Return IDs present in python_students but absent from sql_students.
### Exercise 4: Find SQL-only IDs
Reverse the EXCEPT query to return IDs present in sql_students but absent from python_students.
### Exercise 5: Count common students
Wrap the INTERSECT query in a subquery and count its rows.
### Exercise 6: Use NOT EXISTS
Find Python students whose IDs do not exist in sql_students.
### Exercise 7: Match NULL rows
Use INTERSECT to compare two SELECT statements that each return a NULL integer.
### Exercise 8: Find IDs exclusive to either table
Return IDs that appear in only one of the two tables.
### Exercise 9: Understand column compatibility
Explain why this query fails:
```sql
SELECT student_id, student_name
FROM python_students
EXCEPT
SELECT student_id
FROM sql_students;
```
### Exercise 10: Preserve duplicates
Write a PostgreSQL query using INTERSECT ALL in which the value 7 appears twice in both input results. Predict the number of rows returned.
14. Practice Solutions
### Solution 1: Common IDs
```sql
SELECT student_id
FROM python_students
INTERSECT
SELECT student_id
FROM sql_students
ORDER BY student_id;
```
Expected: 2, 3.
### Solution 2: Common complete rows
```sql
SELECT student_id, student_name
FROM python_students
INTERSECT
SELECT student_id, student_name
FROM sql_students
ORDER BY student_id;
```
Expected: (2, Rahul), (3, Neha).
### Solution 3: Python-only IDs
```sql
SELECT student_id
FROM python_students
EXCEPT
SELECT student_id
FROM sql_students
ORDER BY student_id;
```
Expected: 1, 4.
### Solution 4: SQL-only IDs
```sql
SELECT student_id
FROM sql_students
EXCEPT
SELECT student_id
FROM python_students
ORDER BY student_id;
```
Expected: 5.
### Solution 5: Count common students
```sql
SELECT COUNT(*) AS common_student_count
FROM (
SELECT student_id
FROM python_students
INTERSECT
SELECT student_id
FROM sql_students
) AS common_students;
```
Expected: 2.
### Solution 6: NOT EXISTS
```sql
SELECT p.student_id
FROM python_students AS p
WHERE NOT EXISTS (
SELECT 1
FROM sql_students AS s
WHERE s.student_id = p.student_id
)
ORDER BY p.student_id;
```
Expected: 1, 4.
### Solution 7: NULL rows
```sql
SELECT NULL::INTEGER AS optional_code
INTERSECT
SELECT NULL::INTEGER AS optional_code;
```
Expected: one row containing NULL.
### Solution 8: IDs exclusive to either table
```sql
(
SELECT student_id FROM python_students
EXCEPT
SELECT student_id FROM sql_students
)
UNION
(
SELECT student_id FROM sql_students
EXCEPT
SELECT student_id FROM python_students
)
ORDER BY student_id;
```
Expected: 1, 4, 5.
### Solution 9: Column compatibility
The first SELECT returns two columns and the second returns one. Both SELECT statements must return the same number of columns.
### Solution 10: INTERSECT ALL
```sql
SELECT value
FROM (VALUES (7), (7)) AS a(value)
INTERSECT ALL
SELECT value
FROM (VALUES (7), (7)) AS b(value);
```
Expected: two rows containing 7. Each input contains two occurrences, so the smaller occurrence count is two.
15. Knowledge Check: MCQs
Choose the best answer for each question.
16. SQL Interview Questions and Answers
### 1. What is INTERSECT?
INTERSECT returns distinct complete rows that appear in both query results.
### 2. What is EXCEPT?
EXCEPT returns distinct complete rows from the first query result that do not appear in the second.
### 3. What is the difference between UNION and INTERSECT?
UNION combines distinct rows from either result. INTERSECT returns only rows shared by both results.
### 4. Is EXCEPT directional?
Yes. A EXCEPT B and B EXCEPT A can produce different results.
### 5. What requirements apply to INTERSECT and EXCEPT?
Each SELECT must return the same number of columns, with compatible corresponding data types.
### 6. Are columns matched by name?
No. Corresponding columns are compared by position.
### 7. Do INTERSECT and EXCEPT remove duplicates?
Their default forms eliminate duplicate complete rows.
### 8. What do INTERSECT ALL and EXCEPT ALL do?
They preserve occurrence counts according to multiset rules rather than reducing every result to distinct rows.
### 9. How does INTERSECT compare with INNER JOIN?
INTERSECT returns shared selected rows. INNER JOIN combines matching records and can return columns from both tables.
### 10. How does EXCEPT compare with NOT EXISTS?
EXCEPT compares complete selected rows. NOT EXISTS checks whether a row satisfying a specified matching condition exists.
### 11. Can NULL values match in INTERSECT?
Yes. Corresponding NULL values are treated as not distinct for set-operation comparison.
### 12. Why can NOT EXISTS produce a different result for NULL?
If its matching condition uses ordinary equality, NULL = NULL evaluates to UNKNOWN, so a matching row might not be found. Null-safe equality can be expressed with IS NOT DISTINCT FROM in PostgreSQL.
### 13. Does EXCEPT guarantee sorted output?
No. Use ORDER BY.
### 14. Can INTERSECT compare results from different tables?
Yes, if the SELECT statements return compatible result structures.
### 15. How would you find students enrolled in one course but not another?
Use EXCEPT on the student identifiers, or use NOT EXISTS with a matching condition on the identifiers.
### 16. When would you use INTERSECT ALL?
When the number of occurrences of a common row matters and should be preserved up to the smaller input count.
### 17. How do you find rows exclusive to either result?
Combine A EXCEPT B and B EXCEPT A with UNION, or UNION ALL when duplicate occurrence counts must be preserved and the query is designed accordingly.
### 18. Does an index guarantee better performance for EXCEPT?
No. Indexes may help some plans, but the optimizer chooses operations based on the query and available statistics.
### 19. How do you inspect performance?
Use EXPLAIN for the plan and EXPLAIN ANALYZE for actual execution measurements.
### 20. Can you replace every EXCEPT query with NOT EXISTS?
No. The selected columns, duplicate behavior, and NULL semantics must be considered before substituting one form for the other.
17. Summary and Final Checklist
## Summary
- INTERSECT finds distinct complete rows common to both results.
- EXCEPT finds distinct complete rows in the first result but not the second.
- EXCEPT is directional.
- Corresponding columns must have compatible types and matching positions.
- Set operations compare selected rows, not automatically primary keys.
- Default INTERSECT and EXCEPT eliminate duplicates.
- PostgreSQL supports INTERSECT ALL and EXCEPT ALL for multiplicity-aware results.
- NULL values have special comparison behavior in set operations.
- NOT EXISTS is useful for anti-match queries but can differ from EXCEPT.
- ORDER BY is required for a guaranteed final ordering.
- Use EXPLAIN to inspect query plans and avoid unsupported performance assumptions.
## Before moving to the next lesson
- [ ] Explain INTERSECT in simple language.
- [ ] Explain the direction of EXCEPT.
- [ ] Find shared student IDs.
- [ ] Find rows present in one result but absent from another.
- [ ] Explain why set operations require compatible columns.
- [ ] Compare EXCEPT with NOT EXISTS.
- [ ] Describe how NULL values behave.
- [ ] Explain the purpose of ALL variants.
- [ ] Sort a combined result with ORDER BY.
- [ ] Inspect a query plan with EXPLAIN.
**Next:** Continue to the next lesson in Subqueries and Set Operations.
18. Common Mistakes
Avoid these mistakes when comparing query results.
Common pitfalls to avoid
Real debugging lessons and exact syntax contrasts to avoid runtime bugs
#1 Reversing the EXCEPT inputs
Why it happens: The query is read as a general difference instead of a directional difference.
Key takeaway: EXCEPT returns rows from the first result that are absent from the second. Confirm which direction the requirement needs.
#2 Assuming set operations compare only IDs
Why it happens: Additional selected columns affect whether complete rows match.
Key takeaway: Select the columns that define the intended comparison. These two queries answer different questions.
#3 Assuming EXCEPT and NOT EXISTS are always interchangeable
Why it happens: Their duplicate handling and NULL-matching behavior can differ.
Key takeaway: Compare the intended output, selected columns, duplicates, and NULL semantics before substituting one pattern for another.
Ready for hands-on challenges?
Test your skills with shuffled coding challenges in the Practice Arena.
Key takeaways
- INTERSECT returns distinct complete rows present in both query results.
- EXCEPT returns distinct complete rows from the first query that are absent from the second.
- Both operators compare complete selected rows, not just one column.
- Corresponding columns must have compatible data types and matching positions.
- INTERSECT and EXCEPT remove duplicates by default.
- PostgreSQL supports INTERSECT ALL and EXCEPT ALL, which preserve multiplicity according to multiset rules.
- EXCEPT is directional: reversing the queries can change the result.
- Set operations do not guarantee result order without ORDER BY.
- NOT EXISTS can express many anti-match queries, but its semantics depend on the selected columns and matching condition.
- Choose the clearest correct operation, then examine query plans when performance matters.