Why join query give different result than subquery?

Question

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?

They return a different number of rows between they are completely different queries — user330315
– user330315, Commented Mar 4, 2021 at 7:33

Laurenz Albe · Accepted Answer · 2021-03-04 07:21:28Z

2

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.

answered Mar 4, 2021 at 7:21

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why join query give different result than subquery?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related