MySQL ANY and ALL

Danger781

So I have an exam on MySQL tomorrow, and I'm confused about one thing.. The difference between ANY and ALL..

For example:
Write an SQL query that returns the model of those laptops that are slower than any PC.

SELECT model FROM Laptop WHERE speed < ANY (SELECT speed FROM PC);
10 rows in set

SELECT model FROM Laptop WHERE speed < ALL (SELECT speed FROM PC);
Empty set

Logically speaking this should return the required result:
SELECT model FROM Laptop WHERE Laptop.speed < (SELECT MIN(speed) FROM PC);
But it returns empty set?

jmcc

Danger781 wrote: »

So I have an exam on MySQL tomorrow, and I'm confused about one thing.. The difference between ANY and ALL..

For example:
Write an SQL query that returns the model of those laptops that are slower than any PC.

SELECT model FROM Laptop WHERE speed < ANY (SELECT speed FROM PC);
10 rows in set

SELECT model FROM Laptop WHERE speed < ALL (SELECT speed FROM PC);
Empty set

Logically speaking this should return the required result:
SELECT model FROM Laptop WHERE Laptop.speed < (SELECT MIN(speed) FROM PC);
But it returns empty set?

I've rarely if ever used ALL and ANY. But logically, wouldn't the speed(s) of the laptops have to be less than ALL of the values in the SELECT from PC?

Since at least one laptop could be faster than a PC, then that logical condition is going to be false (the result of the ANY query show that there are 10 possibly slower laptops).

This is the MySQL manual page on ALL:
http://dev.mysql.com/doc/refman/5.5/en/all-subqueries.html

The other thing to check is that the values in Laptop.speed and PC.speed are in the same units (GHz/GHz or MHz/MHz).

But it may be that the ALL condition is the root of the problem. In English, to satisfy the condition, the laptop speed would have to be less than ALL (each of the values). It is not the same as the MIN which selects only the lowest value. Hopefully someone who knows more about this MySQL feature could confirm or clarify. It can be a tricky function.

Regards...jmcc

Danger781

So basically you always use ALL?

Seth Brundle

...WHERE speed < ANY(... means that the speed must be less than any of the values returned by the subselect

...WHERE speed < ALL (... means that the speed must be less than the speed of all values returned by the query. This would be the normal implementation and could also be rewritten as your query uning the MIN function.

Again like jmcc, I don't recall ever using either.

jmcc

Danger781 wrote: »

So basically you always use ALL?

I don't think I've ever used it. But it is a logical operator and since ALL laptop.speed(s) are not ALL less than pc.speed(s), you've got a possible empty set result. (If I am reading it right.)

This is the key quote from the MySQL manual:
"The word ALL, which must follow a comparison operator, means “return TRUE if the comparison is TRUE for ALL of the values in the column that the subquery returns."

It might be easier to rewrite it as a simple join rather than a subquery.

Regards...jmcc

Danger781

So you mean do it like..

SELECT Laptop.model FROM PC, Laptop WHERE Laptop.speed < PC.speed

jmcc

Danger781 wrote: »

So you mean do it like..

SELECT Laptop.model FROM PC, Laptop WHERE Laptop.speed < PC.speed

You could also use distinct(Laptop.model) to limit results. (It should work but you should always test it.)

Regards...jmcc

Danger781

Thanks a million

jmcc

Danger781 wrote: »

Thanks a million

The MIN approach should be better (and a bit more complex) though as there is a risk that the SELECT will stop at the first value and as a result could be a bit unpredictable. It is not enough to know that your query gives the "right" results - you have to know why it gives those results.

Though ANY should work as well as it is wideranging. If it is for an exam, follow your lecture notes or ask a lecturer. It may be the lecturer correcting the paper.

Regards...jmcc