Search code examples
sqlany

Difference between using (operator) and using (operator) ANY

I cannot distinguish when to use operators like = only and when to use = ANY. They seem similar to me. For Example, Here are two tables and the question is: Write a query to display the salesmen which name are alphabetically lower than the name of the customers. My answer is

SELECT * FROM salesman WHERE name < (SELECT cust_name FROM customer);

But the correct answer is

SELECT * FROM salesman WHERE name < ANY (SELECT cust_name FROM customer);

, meaning I missed a ANY. Can anyone tell me why I'm wrong and when to use ANY and when not to. enter image description here

Solution 
  • WHERE name < (SELECT cust_name FROM customer)

    This condition will return an error if there is more than one row in customer. Why? The < operator expects a scalar value. A subquery can be used -- but only if it returns one column and at most one row.

    SQL offers ANY and ALL to support such constructs (and SOME which is the same as ANY). As the question is phrased you want ALL:

    WHERE name < ALL (SELECT cust_name FROM customer)

    If you wanted a name less than the maximum cust_name, you could use:

    WHERE name < ANY (SELECT cust_name FROM customer)

    In general, ANY and ALL are not commonly used. Most people find aggregation simpler to follow:

    WHERE name < (SELECT MIN(cust_name) FROM customer)
WHERE name < (SELECT MAX(cust_name) FROM customer)

    There is a subtle difference when customer is empty, but that is rarely important (or the aggregation form does what is intended).