John Smith John Smith - 3 months ago 9
SQL Question

How to express nested SELECTS as a simpler JOIN

I have three tables:

customers: id, name
transactions: id_customer, id_item
items: id, name


I want to write a simple query that will return the names of all customers who ordered an item with the name 'apple'

I know I can do something like:

SELECT
name
FROM
customers
WHERE id IN (SELECT
id_customer
FROM
transactions
WHERE id_item IN (SELECT
id
FROM
items
WHERE name = 'apple'))


This may not be too bad, but it seems convoluted and like there might be a more straightforward way to do this with a join?

Answer

Yes, it's definitely better to use a join in this case. Try this:

SELECT DISTINCT
    A.name
FROM 
    customers A INNER JOIN
    transactions B ON A.id = B.id_customer INNER JOIN
    items C ON B.id_item = C.id
WHERE C.name = 'apple'

In this way, only customers who placed an order containing 'apple's will be shown.

Comments