EXCEPT (MINUS) for differences in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find rows in TableA not in TableB using EXCEPT

Given two tables TableA and TableB with a single column id, what is the result of this query?

SELECT id FROM TableA
EXCEPT
SELECT id FROM TableB;

Assume:

TableA: {1, 2, 3, 4}
TableB: {3, 4, 5}

SQL

SELECT id FROM TableA
EXCEPT
SELECT id FROM TableB;

A {1, 2}

B {3, 4}

C {5}

D {1, 2, 3, 4, 5}

💡 Hint

EXCEPT returns rows from the first query that are not in the second.

The query returns ids in TableA that are not in TableB. Since 1 and 2 are only in TableA, the result is {1, 2}.

❓ query_result

intermediate

Difference between EXCEPT and NOT IN

Consider the following two queries on tables Employees and Managers with column employee_id:

Query 1:
SELECT employee_id FROM Employees
EXCEPT
SELECT employee_id FROM Managers;

Query 2:
SELECT employee_id FROM Employees
WHERE employee_id NOT IN (SELECT employee_id FROM Managers);

Which statement is true about their results?

A Both queries return the same result set.

B Query 1 returns duplicates, Query 2 does not.

C Query 2 returns NULL values if present, Query 1 does not.

D Query 1 is faster but returns different results than Query 2.

💡 Hint

Think about how EXCEPT and NOT IN handle duplicates and NULLs.

Both queries return the set of employee_ids in Employees but not in Managers. EXCEPT removes duplicates by default, and NOT IN also filters out those in Managers. If Managers.employee_id has NULLs, NOT IN may behave differently, but assuming no NULLs, results match.

📝 Syntax

advanced

Identify the syntax error in EXCEPT usage

Which of the following SQL queries will cause a syntax error?

A SELECT id FROM Table1 EXCEPT SELECT id FROM Table2;

B SELECT id, name FROM Table1 EXCEPT SELECT id, name FROM Table2;

C SELECT id FROM Table1 EXCEPT ALL SELECT id FROM Table2;

D SELECT id FROM Table1 EXCEPT SELECT name FROM Table2;

💡 Hint

Check if the columns in both SELECT statements match in number and type.

Option D has mismatched columns: first SELECT returns 'id', second returns 'name'. EXCEPT requires same number and compatible types of columns.

❓ optimization

advanced

Optimizing difference queries with EXCEPT

You want to find rows in Orders that are not in ArchivedOrders. Which query is generally more efficient?

A SELECT * FROM Orders EXCEPT SELECT * FROM ArchivedOrders;

B SELECT * FROM Orders WHERE order_id NOT IN (SELECT order_id FROM ArchivedOrders);

C SELECT * FROM Orders LEFT JOIN ArchivedOrders ON Orders.order_id = ArchivedOrders.order_id WHERE ArchivedOrders.order_id IS NULL;

D SELECT * FROM Orders WHERE EXISTS (SELECT 1 FROM ArchivedOrders WHERE ArchivedOrders.order_id = Orders.order_id);

💡 Hint

Consider how joins and indexes affect performance.

Option C uses a LEFT JOIN with IS NULL filter, which is often optimized well by databases using indexes. EXCEPT and NOT IN can be less efficient on large datasets.

🧠 Conceptual

expert

Behavior of EXCEPT with duplicates and NULLs

Given two tables T1 and T2 with a single column val:

T1: {1, 2, 2, NULL}
T2: {2, NULL}

What is the result of the query:

SELECT val FROM T1
EXCEPT
SELECT val FROM T2;

SQL

SELECT val FROM T1
EXCEPT
SELECT val FROM T2;

A {} (empty set)

B {1}

C {1, 2}

D {1, NULL}

💡 Hint

Remember how EXCEPT treats duplicates and NULL values.

EXCEPT removes duplicates and excludes any values present in the second query. NULLs are treated as equal, so NULL in T1 is excluded because NULL is in T2. The only value left is 1.

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