JuniorCodeCommonNot answered yet
Query customers who never placed an order
Return every customer that has no matching row in the orders table.
-- Customers(id, name)
-- Orders(id, customer_id)
-- write your query hereWrite the query.
Left-join orders and keep the unmatched rows: SELECT c.id, c.name FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id WHERE o.customer_id IS NULL. The LEFT JOIN ... IS NULL anti-join keeps exactly the customers with no matching order.
- ✗Using
!=in a join condition to mean 'no match' - ✗Trusting
NOT INwhen the subquery can contain NULLs - ✗Inner-joining (which drops the no-order customers) then counting zero
- →Why can
NOT INgive wrong results when the subquery returns a NULL? - →How does
NOT EXISTSexpress the same anti-join safely?
Contents
Task
Return the customers in Customers that have no row in Orders.
Solution
SELECT c.id, c.name
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;Key points
LEFT JOINkeeps every customer; unmatched ones getNULLon the orders side.WHERE o.customer_id IS NULLkeeps exactly those with no order — the anti-join.NOT EXISTSis a safe alternative;NOT INbreaks on aNULLin the subquery.
Contents
