JOIN query and ANY query produced different results

I am new to SQL and I can’t figure out why the following two queries would have different results. The purpose is to list product names if there are any records in the order details table that quantity = 10.

SELECT products.ProductName
FROM products
INNER JOIN `order details` ON `order details`.`ProductID` = products.ProductID
WHERE `order details`.`Quantity` = 10;

SELECT ProductName
FROM products
WHERE ProductID = ANY (SELECT ProductID FROM `order details` WHERE Quantity = 10);

The first statement gives more results than the second one. I am using the “Northwind sample database” in MySQL to test these SQL statements.

Can anyone tell me why these two statements would have different results, and which one is correct? Thanks a lot!

Answer

JOIN can have two unexpected side effects:

• Rows can be filtered out because there is no match (and if the expressions used for matching are NULL then then are almost always filtered out).
• Rows can be multiplied because there are multiple matches.

Whenever you use JOIN, you should be thinking about these. You are not taking the second condition into account — there are multiple matches.

The WHERE clause filters rows out. It does not generate new rows. So duplicates in the second table do not multiple rows.