EXCEPT equivalent in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this EXCEPT equivalent query?

Given two tables Employees and Managers with a single column name, what does the following query return?

SELECT name FROM Employees WHERE name NOT IN (SELECT name FROM Managers);

MySQL

CREATE TABLE Employees (name VARCHAR(20));
INSERT INTO Employees VALUES ('Alice'), ('Bob'), ('Charlie');
CREATE TABLE Managers (name VARCHAR(20));
INSERT INTO Managers VALUES ('Bob');

A [{"name": "Bob"}]

B [{"name": "Alice"}, {"name": "Charlie"}]

C [{"name": "Alice"}, {"name": "Bob"}, {"name": "Charlie"}]

D []

💡 Hint

Think about which employees are not managers.

The query selects employees whose names are not in the Managers table, so it returns Alice and Charlie.

❓ query_result

intermediate

Which query returns rows in TableA but not in TableB?

You want to find all rows in TableA that do not appear in TableB. Which of these queries correctly does that in MySQL?

A SELECT * FROM TableA WHERE NOT EXISTS (SELECT 1 FROM TableB WHERE TableB.id = TableA.id);

B SELECT * FROM TableA EXCEPT SELECT * FROM TableB;

C SELECT * FROM TableA WHERE id NOT IN (SELECT id FROM TableB);

D SELECT * FROM TableB WHERE id NOT IN (SELECT id FROM TableA);

💡 Hint

MySQL does not support EXCEPT. Use a subquery with NOT EXISTS or NOT IN.

Option A uses NOT EXISTS to find rows in TableA that have no matching id in TableB, which is the correct EXCEPT equivalent.

📝 Syntax

advanced

Which query will cause a syntax error in MySQL?

Identify the query that will cause a syntax error in MySQL when trying to find rows in Table1 not in Table2.

A SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.id = Table2.id WHERE Table2.id IS NULL;

B SELECT * FROM Table1 WHERE NOT EXISTS (SELECT 1 FROM Table2 WHERE Table2.id = Table1.id);

C SELECT * FROM Table1 WHERE id NOT IN (SELECT id FROM Table2);

D SELECT * FROM Table1 EXCEPT SELECT * FROM Table2;

💡 Hint

MySQL does not support EXCEPT keyword.

MySQL does not support the EXCEPT keyword, so option D will cause a syntax error.

❓ optimization

advanced

Which query is the most efficient EXCEPT equivalent in MySQL for large tables?

You want to find rows in Orders that are not in ShippedOrders. Which query is generally the most efficient in MySQL for large datasets?

A SELECT * FROM Orders WHERE id NOT IN (SELECT id FROM ShippedOrders);

B SELECT * FROM Orders WHERE NOT EXISTS (SELECT 1 FROM ShippedOrders WHERE ShippedOrders.id = Orders.id);

C SELECT Orders.* FROM Orders LEFT JOIN ShippedOrders ON Orders.id = ShippedOrders.id WHERE ShippedOrders.id IS NULL;

D SELECT * FROM Orders EXCEPT SELECT * FROM ShippedOrders;

💡 Hint

Consider how joins and subqueries perform on large tables.

LEFT JOIN with IS NULL is often more efficient than NOT IN or NOT EXISTS on large tables in MySQL.

🧠 Conceptual

expert

Why does NOT IN sometimes produce unexpected results compared to NOT EXISTS in MySQL?

Consider these two queries to find rows in Products not in Discontinued:

1) SELECT * FROM Products WHERE id NOT IN (SELECT id FROM Discontinued);
2) SELECT * FROM Products WHERE NOT EXISTS (SELECT 1 FROM Discontinued WHERE Discontinued.id = Products.id);

Why might query 1 return no rows even if some products are not discontinued?

A Because NOT IN returns no rows if the subquery returns any NULL values.

B Because NOT EXISTS is slower and less reliable than NOT IN.

C Because NOT IN only works with numeric columns.

D Because NOT EXISTS requires indexes to work correctly.

💡 Hint

Think about how NULL values affect NOT IN.

If the subquery returns NULL, NOT IN returns no rows because NULL comparison is unknown, causing unexpected results.