EXCEPT (MINUS) for differences in SQL - Step-by-Step Execution

Concept Flow - EXCEPT (MINUS) for differences

Start with Table A

↓

Compare each row in A

↓

Is row in Table B?

Yes→Exclude row

No↓

Include row in result

↓

Repeat for all rows in A

↓

Return all included rows

The EXCEPT operation returns rows from the first table that are not present in the second table by checking each row and excluding those found in the second.

Execution Sample

SQL

SELECT name FROM employees
EXCEPT
SELECT name FROM managers;

This query returns names of employees who are not managers.

Execution Table

Step	Row from employees	Is in managers?	Action	Result set
1	Alice	No	Include	Alice
2	Bob	Yes	Exclude	Alice
3	Charlie	No	Include	Alice, Charlie
4	Diana	Yes	Exclude	Alice, Charlie
5	Eve	No	Include	Alice, Charlie, Eve
6	End of employees	-	-	Final result: Alice, Charlie, Eve

💡 All rows from employees checked; those found in managers excluded.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
Current Row	None	Alice	Bob	Charlie	Diana	Eve	End
Result Set	Empty	Alice	Alice	Alice, Charlie	Alice, Charlie	Alice, Charlie, Eve	Alice, Charlie, Eve

Key Moments - 2 Insights

Why is Bob excluded even though he is in the employees table?

Bob is excluded because he also appears in the managers table, as shown in execution_table row 2 where 'Is in managers?' is Yes, so the action is to exclude.

What happens if a row is not found in the second table?

If a row is not found in the second table, it is included in the result set, as seen with Alice in row 1 and Charlie in row 3 of the execution_table.

Visual Quiz - 3 Questions

Look at the execution table, what is the result set after step 3?

A Alice, Bob, Charlie

B Alice, Charlie

C Charlie

D Alice

💡 Hint

Check the 'Result set' column at step 3 in the execution_table.

At which step does the condition 'Is in managers?' become Yes for the first time?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'Is in managers?' column in the execution_table to find the first Yes.

If 'Eve' was also in the managers table, how would the final result set change?

A Eve would be excluded from the final result

B Eve would be included twice

C No change, Eve stays included

D Query would return an error

💡 Hint

Refer to how rows found in managers are excluded in the execution_table.

Concept Snapshot

EXCEPT returns rows from the first query that are not in the second.
Syntax: SELECT columns FROM table1 EXCEPT SELECT columns FROM table2;
It compares rows and excludes those found in the second set.
Useful to find differences between two tables.
Rows must have matching columns and types.
Result has unique rows from the first query only.

Full Transcript

The EXCEPT operation in SQL compares two sets of rows. It returns only those rows from the first set that do not appear in the second. For example, if we select names from employees and exclude those also found in managers, we get employees who are not managers. The process checks each row in the first table, sees if it exists in the second, and includes it only if it does not. This is shown step-by-step in the execution table, where rows like Alice and Charlie are included because they are not in managers, while Bob and Diana are excluded because they are. This helps find differences between two tables easily.