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.