Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

MySQL ANY and ALL

  • 03-05-2011 7:22pm
    #1
    Registered Users, Registered Users 2 Posts: 5,697 ✭✭✭


    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?

    :confused::confused:


Comments

  • Registered Users, Registered Users 2 Posts: 7,530 ✭✭✭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?

    :confused::confused:
    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


  • Registered Users, Registered Users 2 Posts: 5,697 ✭✭✭Danger781


    So basically you always use ALL?


  • Moderators, Politics Moderators Posts: 42,144 Mod ✭✭✭✭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.


  • Registered Users, Registered Users 2 Posts: 7,530 ✭✭✭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


  • Registered Users, Registered Users 2 Posts: 5,697 ✭✭✭Danger781


    So you mean do it like..

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


  • Advertisement
  • Registered Users, Registered Users 2 Posts: 7,530 ✭✭✭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


  • Registered Users, Registered Users 2 Posts: 5,697 ✭✭✭Danger781


    Thanks a million :)


  • Registered Users, Registered Users 2 Posts: 7,530 ✭✭✭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


Advertisement