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?
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.
