I looked at a query of "ANY" from a tutorial site which was like:
SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);
This query is returning 31 rows and no duplicates.
After this I tried to apply same query using Joins but I was unable to get result coming from above query.
Join query I used:
SELECT Products.ProductName
FROM Products
LEFT JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID
WHERE OrderDetails.Quantity = 10
ORDER BY Products.ProductName;
This is returning 44 rows, and has duplicates included.
After I used DISTINCT in this join query with ProductName, I got the desired result.
Hence I want to know - does "ANY" clause produce distinct records?
PS: Same record came in both Join queries (with and without distinct) with Inner Join as Well.
Hence I want to know - does "ANY" clause produce distinct records?
No. It is actually the opposite. The records being chosen are those in the FROM clause. So, in the first query, there are no duplicates in Products. The WHERE clause is never going to generate duplicate records. That is not a property of ANY in particular; it is also true of IN and EXISTS and any other comparison operation.
What is opposite is that the JOIN does produce duplicate records. That is what you are seeing in the second query. The table OrderDetails has multiple rows for a given product.
Note that ANY (and IN) do actually implement a type of JOIN called a semi-join. So, there is a relationship between what these operators do and JOINs in the FROM clause. However, semi-joins and anti-joins are different from inner and outer joins that are defined in the FROM clause.