INTERSECT and EXCEPT in PostgreSQL - Step-by-Step Execution

Concept Flow - INTERSECT and EXCEPT

Start with Query A

↓

Start with Query B

↓

Find common rows

↓

Return result set

↓

End

This flow shows how two queries produce sets of rows, then INTERSECT returns only rows in both sets, while EXCEPT returns rows in the first set but not in the second.

Execution Sample

PostgreSQL

SELECT name FROM fruits
INTERSECT
SELECT name FROM tropical_fruits;

This query returns fruit names that appear in both fruits and tropical_fruits tables.

Execution Table

Step	Query A Result	Query B Result	Operation	Result After Step
1	['apple', 'banana', 'mango', 'orange']	['mango', 'pineapple', 'banana']	Start	N/A
2	N/A	N/A	INTERSECT	Find common rows between A and B
3	N/A	N/A	Result	['banana', 'mango']
4	N/A	N/A	EXCEPT	Find rows in A not in B
5	N/A	N/A	Result	['apple', 'orange']
6	N/A	N/A	End	Execution stops

💡 Execution stops after returning the result set for the chosen operation.

Variable Tracker

Variable	Start	After INTERSECT	After EXCEPT	Final
Query A Result	N/A	['apple', 'banana', 'mango', 'orange']	['apple', 'banana', 'mango', 'orange']	N/A
Query B Result	N/A	['mango', 'pineapple', 'banana']	['mango', 'pineapple', 'banana']	N/A
Result Set	N/A	['banana', 'mango']	['apple', 'orange']	Depends on operation

Key Moments - 3 Insights

Why does INTERSECT return only 'banana' and 'mango'?

Because INTERSECT returns only rows that appear in both Query A and Query B results, as shown in execution_table row 3.

Why does EXCEPT return 'apple' and 'orange'?

Because EXCEPT returns rows in Query A that are not in Query B, as shown in execution_table row 5.

Are duplicates removed in INTERSECT and EXCEPT results?

Yes, both INTERSECT and EXCEPT return distinct rows only, so duplicates are removed automatically.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result set after the INTERSECT operation?

A ['banana', 'mango']

B ['apple', 'orange']

C ['mango', 'pineapple', 'banana']

D ['apple', 'banana', 'mango', 'orange']

💡 Hint

Check the 'Result After Step' column at Step 3 in the execution_table.

At which step does the EXCEPT operation produce its result?

A Step 2

B Step 5

C Step 3

D Step 1

💡 Hint

Look at the 'Operation' column for EXCEPT and find the corresponding 'Result After Step'.

If 'banana' was removed from Query B Result, what would INTERSECT return?

A ['banana', 'mango']

B ['apple', 'orange']

C ['mango']

D []

💡 Hint

Refer to variable_tracker to see which rows are common between Query A and Query B.

Concept Snapshot

INTERSECT and EXCEPT combine two query results.
INTERSECT returns rows common to both queries.
EXCEPT returns rows in first query not in second.
Both remove duplicates automatically.
Syntax:
SELECT ... FROM ... INTERSECT SELECT ... FROM ...;
SELECT ... FROM ... EXCEPT SELECT ... FROM ...;

Full Transcript

This visual execution shows how INTERSECT and EXCEPT work in PostgreSQL. First, two queries produce result sets. INTERSECT finds rows present in both sets, returning only those common rows. EXCEPT finds rows in the first set that are not in the second, returning those unique to the first. Both operations remove duplicates automatically. The execution table traces these steps with example fruit names. Key moments clarify why certain rows appear in results. The quiz tests understanding by referencing these visuals.