Problem
Task
Return product titles for products that do not appear in any order.
Schema
Table Schema
products(id, title, category)
orders(id, product_id, customer_id)Input
Sample Data
products
| id | title | category |
|---|---|---|
| 10 | Mini Gizmo | Gizmo |
| 11 | Desk Mat | Office |
| 12 | USB Hub | Accessories |
orders
| id | product_id | customer_id |
|---|---|---|
| 601 | 10 | 1 |
| 602 | 11 | 2 |
Output
Expected Output
| title |
|---|
| USB Hub |
Answer
Check Your Solution
Show Answer and Explanation
Correct Answer
SELECT p.title
FROM products AS p
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.product_id = p.id
);Explanation
NOT EXISTS checks whether the correlated subquery finds any order for the current product. If no row exists, the product is returned.
Common Mistakes
- Using NOT IN without considering NULL values in the subquery result.
- Forgetting to correlate the subquery with p.id.
- Returning order rows instead of product rows.
Concepts
Related Concepts
NOT EXISTS
Correlated Subqueries
Anti Join
Subquery
Next practice