Can I use '= ALL' in SQL Query?

Question

I am trying to use an '= ALL' using a parameter and a set of results from a sub-query, like this:

SELECT table.something
FROM Table t
WHERE t.param = ALL (... sub-query...)

is this possible? because I know what the result should be and I'm not getting any results at all...

Answer 1

Yes, it is possible:

http://dev.mysql.com/doc/refman/5.0/en/all-subqueries.html

If you're not getting the results you expect, I'm guessing there is an issue with the query. The way it is currently written, all of the results in the sub-query must match t.param's value (which doesn't make a whole lot of sense out of context).

Answer 2

It's only makes sense if the subquery only ever returns a single value.

If the subquery returns more than one value, you will not get any matching rows. There is no value that can be equal to two other values simultaneously.

If the subquery for example returns 1 and 2, and your table contains the value 2, it won't match because the value 2 is not equal to both 1 and 2.

So, using the ALL keyword with the = operator is actually pretty useless. I think that you want to use the ANY keyword or the IN operator instead.