EXCEPT equivalent in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all customers who have placed orders but exclude those who have returned items.

MySQL

SELECT customer_id FROM orders WHERE status = 'placed' [1] SELECT customer_id FROM returns WHERE status = 'returned';

Drag options to blanks, or click blank then click option'

AMINUS

BINTERSECT

CUNION

DEXCEPT

Attempts:

3 left

💡 Hint

Common Mistakes

Using UNION instead of EXCEPT returns all rows from both queries.

Using INTERSECT returns only common rows, not the difference.

The EXCEPT keyword returns rows from the first query that are not in the second query. MySQL does not support EXCEPT directly, but this is the standard SQL syntax.

2fill in blank

medium

Complete the code to find products sold but not returned using a LEFT JOIN and filtering NULLs.

MySQL

SELECT p.product_id FROM products p LEFT JOIN returns r ON p.product_id = r.product_id WHERE r.product_id [1];

Drag options to blanks, or click blank then click option'

AIS NOT NULL

BIS NULL

C= NULL

D<> NULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using '= NULL' or '<> NULL' which are invalid in SQL.

Using IS NOT NULL which finds matched rows instead.

When using LEFT JOIN, rows from the left table without matches in the right table have NULLs. Filtering with IS NULL finds products not returned.

3fill in blank

hard

Fix the error in the query that tries to find employees who worked on projects but not on project 5.

MySQL

SELECT employee_id FROM project_assignments WHERE project_id != [1];

Drag options to blanks, or click blank then click option'

A5

BNULL

C'5'

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around numeric IDs causing type mismatch.

Using NULL with != which always returns false.

The query should exclude project 5 by using project_id != 5. Using NULL or quotes incorrectly causes errors or wrong results.

4fill in blank

hard

Fill both blanks to create a query that selects all students who took Math but not Science using NOT EXISTS.

MySQL

SELECT student_id FROM enrollments e1 WHERE course = [1] AND NOT EXISTS (SELECT 1 FROM enrollments e2 WHERE e2.student_id = e1.student_id AND course = [2]);

Drag options to blanks, or click blank then click option'

A'Math'

B'Science'

C'History'

D'English'

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the courses in the blanks.

Forgetting quotes around course names.

The query finds students enrolled in Math but excludes those also enrolled in Science using NOT EXISTS.

5fill in blank

hard

Fill all three blanks to write a query that lists all employees who are in the sales department but not in the marketing department using a subquery with NOT IN.

MySQL

SELECT employee_id FROM employees WHERE department = [1] AND employee_id NOT IN (SELECT employee_id FROM employees WHERE department = [2] AND status = [3]);

Drag options to blanks, or click blank then click option'

A'Sales'

B'Marketing'

C'Active'

D'Inactive'

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong department names or missing quotes.

Not filtering by status in the subquery.

This query selects employees in Sales who are not in Marketing and have active status in the subquery.

Practice

1. What does the SQL EXCEPT operation do?

easy

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

B. Returns rows common to both queries

C. Returns all rows from both queries combined

D. Deletes rows from the first table

Solution

1. Step 1: Understand the purpose of EXCEPT

   The EXCEPT operation returns rows that appear in the first query but do not appear in the second query.

2. Step 2: Compare with other set operations

   Unlike INTERSECT which returns common rows, EXCEPT excludes rows found in the second set.

3. Final Answer:

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

4. Quick Check:

   EXCEPT = unique rows in first set [OK]

Hint: EXCEPT means "first minus second" rows [OK]

Common Mistakes:

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

2. Which of the following is the correct MySQL syntax to simulate EXCEPT using NOT EXISTS?

easy

A. SELECT * FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table2.id = table1.id);

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

C. SELECT * FROM table1 JOIN table2 ON table1.id = table2.id WHERE table2.id IS NULL;

D. SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);

Solution

1. Step 1: Identify how NOT EXISTS works

   NOT EXISTS checks if a row in table1 has no matching row in table2 based on the condition.

2. Step 2: Match syntax to EXCEPT behavior

   The query selects rows from table1 where no matching id exists in table2, simulating EXCEPT.

3. Final Answer:

   SELECT * FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table2.id = table1.id); -> Option A

4. Quick Check:

   NOT EXISTS filters out matching rows [OK]

Hint: Use NOT EXISTS with matching condition for EXCEPT [OK]

Common Mistakes:

• Using EXCEPT directly in MySQL (unsupported)
• Confusing JOIN with NOT EXISTS logic
• Using IN instead of NOT EXISTS

3. Given tables:
table1(id): 1, 2, 3, 4
table2(id): 3, 4, 5
What is the result of this query?

SELECT id FROM table1 WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table2.id = table1.id);

medium

A. [3, 4]

B. [1, 2]

C. [5]

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

Solution

1. Step 1: Identify ids in table1 not in table2

   table1 has 1,2,3,4; table2 has 3,4,5. IDs 1 and 2 are missing in table2.

2. Step 2: Understand NOT EXISTS filtering

   The query returns ids from table1 where no matching id exists in table2, so 1 and 2 are returned.

3. Final Answer:

   [1, 2] -> Option B

4. Quick Check:

   table1 minus table2 = [1, 2] [OK]

Hint: Find ids in first table missing in second [OK]

Common Mistakes:

• Including ids present in both tables
• Confusing NOT EXISTS with EXISTS
• Returning ids from table2 instead

4. Identify the error in this MySQL query intended to simulate EXCEPT:

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id WHERE table2.id = NULL;

medium

A. LEFT JOIN should be INNER JOIN

B. Missing ON condition in JOIN

C. Using '=' to compare with NULL instead of IS NULL

D. No error, query is correct

Solution

1. Step 1: Understand NULL comparison in SQL

   In SQL, NULL cannot be compared using '='; use IS NULL instead.

2. Step 2: Identify the mistake in WHERE clause

   The condition table2.id = NULL is always false; it should be table2.id IS NULL to find unmatched rows.

3. Final Answer:

   Using '=' to compare with NULL instead of IS NULL -> Option C

4. Quick Check:

   NULL comparison requires IS NULL [OK]

Hint: Use IS NULL, not = NULL, to check for NULL values [OK]

Common Mistakes:

• Using '=' instead of IS NULL
• Confusing JOIN types
• Omitting ON clause in JOIN

5. You want to find customers who placed orders last month but did NOT place orders this month. Which query correctly simulates EXCEPT in MySQL?

hard

A. SELECT customer_id FROM orders LEFT JOIN orders o2 ON orders.customer_id = o2.customer_id WHERE o2.order_date BETWEEN '2024-06-01' AND '2024-06-30';

B. SELECT customer_id FROM orders WHERE order_date BETWEEN '2024-06-01' AND '2024-06-30' EXCEPT SELECT customer_id FROM orders WHERE order_date BETWEEN '2024-05-01' AND '2024-05-31';

C. SELECT customer_id FROM orders WHERE order_date BETWEEN '2024-05-01' AND '2024-06-30';

D. SELECT customer_id FROM orders WHERE order_date BETWEEN '2024-05-01' AND '2024-05-31' AND NOT EXISTS (SELECT 1 FROM orders o2 WHERE o2.customer_id = orders.customer_id AND o2.order_date BETWEEN '2024-06-01' AND '2024-06-30');

Solution

1. Step 1: Identify the time frames and logic

   We want customers who ordered in May but not in June.

2. Step 2: Use NOT EXISTS to exclude June customers

   The query selects May customers and excludes those with June orders using NOT EXISTS subquery.

3. Step 3: Check other options

   SELECT customer_id FROM orders WHERE order_date BETWEEN '2024-06-01' AND '2024-06-30' EXCEPT SELECT customer_id FROM orders WHERE order_date BETWEEN '2024-05-01' AND '2024-05-31'; uses EXCEPT which MySQL does not support; C returns all customers in both months; D returns June customers only.

4. Final Answer:

   SELECT customer_id FROM orders WHERE order_date BETWEEN '2024-05-01' AND '2024-05-31' AND NOT EXISTS (SELECT 1 FROM orders o2 WHERE o2.customer_id = orders.customer_id AND o2.order_date BETWEEN '2024-06-01' AND '2024-06-30'); -> Option D

5. Quick Check:

   NOT EXISTS excludes June orders [OK]

Hint: Use NOT EXISTS with date ranges to exclude recent orders [OK]

Common Mistakes:

• Using EXCEPT directly in MySQL
• Selecting all orders without filtering
• Confusing LEFT JOIN logic