Search code examples
sqlpostgresqljoinsubquerynorthwind

Why join query give different result than subquery?

I am learning PostgreSQL and working with Nortwind database

Now I am testing JOIN and subquery with ANY
I want select all product_name of which exactly 10 were ordered (column quantity from order_details)
So I have 2 different queries:

SELECT product_name FROM products
WHERE product_id = ANY(
    SELECT product_id FROM order_details
    WHERE quantity = 10
)

and

SELECT products.product_name FROM products
JOIN order_details ON order_details.product_id = products.product_id
WHERE order_details.quantity = 10

But they are giving different results!
Firts one gives: Only 60 rows
And the second one gives: 181 rows

Why is that and which result is right?

Solution

  • The first query will output each products row at most once.

    The second query can have several result rows for a single products row: one for each matching order_details row.

    Which of the queries is better depends on your requirements.