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.

Practice

1. What does the SQL EXCEPT operator do?

easy

A. Returns rows from the first query that are not in the second query

B. Returns rows common to both queries

C. Combines all rows from both queries including duplicates

D. Deletes rows from the first table that exist in the second

Solution

1. Step 1: Understand the purpose of EXCEPT

   The EXCEPT operator compares two query results and returns only those rows that appear in the first query but not in the second.

2. Step 2: Differentiate from other set operations

   Unlike INTERSECT which returns common rows, EXCEPT returns unique rows from the first set only.

3. Final Answer:

   Returns rows from the first query that are not in the second query -> Option A

4. Quick Check:

   EXCEPT = difference = unique first query rows [OK]

Hint: EXCEPT returns what first query has but second does not [OK]

Common Mistakes:

• Confusing EXCEPT with INTERSECT
• Thinking EXCEPT deletes rows
• Assuming EXCEPT returns all combined rows

2. Which of the following is the correct syntax to find rows in table1 not in table2 using EXCEPT?

easy

A. SELECT * FROM table1 WHERE NOT IN table2;

B. SELECT * FROM table1 MINUS table2;

C. SELECT * FROM table1 EXCEPT SELECT * FROM table2;

D. SELECT * FROM table1 JOIN table2 EXCEPT;

Solution

1. Step 1: Recall correct EXCEPT syntax

   The EXCEPT operator is used between two SELECT statements: SELECT ... FROM ... EXCEPT SELECT ... FROM ....

2. Step 2: Check each option

   SELECT * FROM table1 EXCEPT SELECT * FROM table2; uses correct syntax with two SELECT statements separated by EXCEPT. SELECT * FROM table1 MINUS table2; is incorrect because MINUS requires SELECT before both tables. SELECT * FROM table1 WHERE NOT IN table2; is invalid syntax. SELECT * FROM table1 JOIN table2 EXCEPT; misuses JOIN and EXCEPT.

3. Final Answer:

   SELECT * FROM table1 EXCEPT SELECT * FROM table2; -> Option C

4. Quick Check:

   Correct EXCEPT syntax = SELECT ... EXCEPT SELECT ... [OK]

Hint: EXCEPT goes between two SELECT queries, no JOIN needed [OK]

Common Mistakes:

• Using EXCEPT without two SELECT statements
• Confusing MINUS syntax with EXCEPT
• Trying to use EXCEPT with JOIN

3. Given these tables:
table1:
id
1
2
3

table2:
id
2
4
5

What is the result of:
SELECT id FROM table1 EXCEPT SELECT id FROM table2;

medium

A. [1, 3]

B. [2]

C. [4, 5]

D. [1, 2, 3, 4, 5]

Solution

1. Step 1: Identify rows in table1

   table1 has ids 1, 2, 3.

2. Step 2: Identify rows in table2

   table2 has ids 2, 4, 5.

3. Step 3: Apply EXCEPT logic

   EXCEPT returns rows in table1 not in table2, so ids 1 and 3 remain.

4. Final Answer:

   [1, 3] -> Option A

5. Quick Check:

   table1 - table2 = [1, 3] [OK]

Hint: Subtract table2 rows from table1 rows [OK]

Common Mistakes:

• Including ids from table2
• Returning common ids instead
• Confusing EXCEPT with UNION

4. What is wrong with this query?
SELECT name FROM employees EXCEPT name FROM managers;

medium

A. EXCEPT cannot be used with column names

B. Missing SELECT keyword before second query

C. Syntax requires JOIN instead of EXCEPT

D. No error, query is correct

Solution

1. Step 1: Check syntax of EXCEPT usage

   EXCEPT requires two full SELECT statements. The second query must start with SELECT.

2. Step 2: Identify error in given query

   The second part is missing SELECT before name FROM managers, causing syntax error.

3. Final Answer:

   Missing SELECT keyword before second query -> Option B

4. Quick Check:

   EXCEPT needs two SELECTs [OK]

Hint: Always write SELECT before both queries with EXCEPT [OK]

Common Mistakes:

• Omitting SELECT in second query
• Using EXCEPT with columns only
• Confusing EXCEPT with JOIN syntax

5. You have two tables:
orders_2023 and orders_2024, both with columns order_id and customer_id.

You want to find all orders from 2023 that were NOT repeated in 2024.

Which query correctly finds these unique 2023 orders?

hard

A. SELECT order_id, customer_id FROM orders_2023 INTERSECT SELECT order_id, customer_id FROM orders_2024;

B. SELECT order_id, customer_id FROM orders_2024 EXCEPT SELECT order_id, customer_id FROM orders_2023;

C. SELECT order_id, customer_id FROM orders_2023 UNION SELECT order_id, customer_id FROM orders_2024;

D. SELECT order_id, customer_id FROM orders_2023 EXCEPT SELECT order_id, customer_id FROM orders_2024;

Solution

1. Step 1: Understand the requirement

   We want orders in 2023 that do NOT appear in 2024.

2. Step 2: Choose correct set operation

   EXCEPT returns rows in first query not in second, so orders_2023 EXCEPT orders_2024 fits.

3. Step 3: Check other options

   SELECT order_id, customer_id FROM orders_2024 EXCEPT SELECT order_id, customer_id FROM orders_2023; reverses order, giving 2024 unique orders. SELECT order_id, customer_id FROM orders_2023 INTERSECT SELECT order_id, customer_id FROM orders_2024; returns common orders. SELECT order_id, customer_id FROM orders_2023 UNION SELECT order_id, customer_id FROM orders_2024; combines all orders.

4. Final Answer:

   SELECT order_id, customer_id FROM orders_2023 EXCEPT SELECT order_id, customer_id FROM orders_2024; -> Option D

5. Quick Check:

   2023 orders minus 2024 orders = unique 2023 orders [OK]

Hint: Use EXCEPT with 2023 first, 2024 second to find unique 2023 orders [OK]

Common Mistakes:

• Swapping table order in EXCEPT
• Using INTERSECT instead of EXCEPT
• Using UNION which combines all rows