EXCEPT equivalent in MySQL - Step-by-Step Execution

Concept Flow - EXCEPT equivalent

Start: Two SELECT queries

↓

Run first SELECT query

↓

Run second SELECT query

↓

Compare results

↓

Remove rows from first query that appear in second

↓

Return remaining rows as result

This flow shows how to get rows from the first query that are not in the second, mimicking EXCEPT by comparing and filtering results.

Execution Sample

MySQL

SELECT id FROM table1
WHERE id NOT IN (SELECT id FROM table2);

This query returns ids from table1 that do not appear in table2, acting like EXCEPT.

Execution Table

Step	Action	Query Part	Intermediate Result	Explanation
1	Run first SELECT	SELECT id FROM table1	[1, 2, 3, 4]	Get all ids from table1
2	Run second SELECT	SELECT id FROM table2	[3, 4, 5]	Get all ids from table2
3	Compare results	Filter table1 ids NOT IN table2 ids	[1, 2]	Remove ids 3 and 4 found in table2
4	Return final result	Output filtered ids	[1, 2]	These ids are only in table1

💡 All ids from table1 checked; those in table2 excluded, so execution stops.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
table1_ids	empty	[1, 2, 3, 4]	[1, 2, 3, 4]	[1, 2]	[1, 2]
table2_ids	empty	empty	[3, 4, 5]	[3, 4, 5]	[3, 4, 5]
result_ids	empty	empty	empty	[1, 2]	[1, 2]

Key Moments - 2 Insights

Why do we use NOT IN instead of EXCEPT in MySQL?

MySQL does not support EXCEPT, so NOT IN with a subquery filters out rows from the first query that appear in the second, as shown in execution_table step 3.

What happens if table2 has NULL values in id?

If table2 has NULLs, NOT IN may return no rows because NULL comparison is unknown; use NOT EXISTS or LEFT JOIN for safer results.

Visual Quiz - 3 Questions

Look at the execution_table, what is the intermediate result after step 2?

A [1, 2, 3, 4]

B [3, 4, 5]

C [1, 2]

D [5, 6]

💡 Hint

Check the 'Intermediate Result' column for step 2 in execution_table.

At which step are rows removed from the first query's results?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look for the step where filtering happens in execution_table.

If table2 had no ids, what would the final result be?

A Same as table2 ids

B Empty set

C Same as table1 ids

D Error

💡 Hint

Refer to variable_tracker for how filtering depends on table2_ids.

Concept Snapshot

EXCEPT equivalent in MySQL:
Use NOT IN or NOT EXISTS to exclude rows from first query found in second.
Example: SELECT * FROM A WHERE id NOT IN (SELECT id FROM B);
MySQL lacks EXCEPT keyword.
Watch out for NULLs in subqueries affecting results.

Full Transcript

This visual execution shows how to mimic the EXCEPT operation in MySQL, which does not support EXCEPT directly. We run two SELECT queries: one on table1 and one on table2. Then we filter the results of table1 to exclude any ids found in table2 using NOT IN. The execution table traces each step: first fetching ids from table1, then from table2, then filtering out common ids, and finally returning the unique ids from table1. The variable tracker shows how the lists of ids change after each step. Key moments clarify why NOT IN is used and the impact of NULL values. The quiz tests understanding of intermediate results and filtering steps. The snapshot summarizes the approach and cautions about NULLs.