Customers Who Never Order

You can find all customers who never order anything by performing a LEFT JOIN between the Customers and Orders tables, and then filtering for the rows where there is no matching record in the Orders table. Here’s the query to achieve this:

1
2
3
4

SELECT c.name AS Customers
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customerId
WHERE o.id IS NULL;

This query joins the Customers and Orders tables on the customer’s ID and then filters the results to include only the customers for whom there is no corresponding order (indicated by a NULL value in the Orders table’s id column).

LEFT JOIN vs JOIN

The difference between LEFT JOIN and JOIN (INNER JOIN) lies in how they handle non-matching rows between the two tables being joined.

  1. LEFT JOIN: A LEFT JOIN returns all the rows from the left table, along with matched rows from the right table. If there’s no match for a row in the left table, the columns for the right table will contain NULL values. Essentially, a LEFT JOIN includes all records from the left table and the matched records from the right table.

  2. JOIN (INNER JOIN): An INNER JOIN returns only the rows for which there is a match in both the left and right tables. If a row from either table has no matching row in the other table, that row will not appear in the result set. An INNER JOIN focuses strictly on the intersection of the two tables where the specified condition is met.

Here’s a brief example to illustrate the difference:

Given two tables:

  • A: +—-+——+ | id | name | +—-+——+ | 1 | Joe | | 2 | Sam | +—-+——+

  • B: +—-+——+ | id | age | +—-+——+ | 1 | 25 | +—-+——+

Using INNER JOIN:

1
2
3

SELECT A.id, A.name, B.age
FROM A
JOIN B ON A.id = B.id;

Result: +—-+——+—–+ | id | name | age | +—-+——+—–+ | 1 | Joe | 25 | +—-+——+—–+

Using LEFT JOIN:

1
2
3

SELECT A.id, A.name, B.age
FROM A
LEFT JOIN B ON A.id = B.id;

Result: +—-+——+——+ | id | name | age | +—-+——+——+ | 1 | Joe | 25 | | 2 | Sam | NULL | +—-+——+——+

As you can see, the LEFT JOIN includes all rows from table A, even if there’s no matching row in table B, whereas the INNER JOIN only includes rows where there’s a match between both tables.