4

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...

Improve this question
4
  • dev.mysql.com/doc/refman/5.0/en/all-subqueries.html Commented Nov 28, 2011 at 17:12
  • With that syntax you will get only the results for whom param is equal to all the elements returned by the sub-query. Commented Nov 28, 2011 at 17:16
  • What's t.param? Wouldn't it have to be an array or something? Commented Nov 28, 2011 at 17:23
  • It may help to illustrate what you'd expect for some given inputs, as it's not crystal clear what you're expecting. Commented Nov 28, 2011 at 17:27

3 Answers 3

Reset to default
4

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).

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Wow - the ALL completely passed me by. That could come in useful for a couple of things I'm trying to achieve.
3

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.

Improve this answer

4 Comments

"It's only makes sense if the subquery only ever returns a single value." -- Can you clarify what you mean, please. Consider: SELECT t.ID FROM MyTable t WHERE t.ID = ALL (SELECT t2.ID FROM MyTable t2 WHERE t2.ID = t.ID); -- is the subquery really only returning one value here?
@onedaywhen: I don't know what it is that you find unclear, you have to specify... Yes, the subquery is only returning one value in your example. The subquery is depending on the value from each specific row, so it will be executed once for each row in t, and each time the value of t2.ID can not be anything other than equal to t.ID, as that is what you specified in the condition.
"it will be executed once for each row in t, and each time the value of t2.ID" -- so if there are three distinct values for ID in MyTable then the same subquery will return three different values, right? That is quite different from "only ever returns a single value", don't you think?
@onedaywhen: No, it's not. To "only ever return a single value" is not the same as to "always return the same single value".
-1

This is the general form used, I've not heard of ALL:

SELECT table.something
FROM Table t
WHERE t.param IN (SELECT param FROM Table2 WHERE somecriteria='somevalue')

For a names table of this:

ID | firstname 
---+----------
 1 | alice
 2 | bob
 3 | jane
 4 | sue

The query:

SELECT * FROM names WHERE firstname in ('alice', 'jane');

Will return this:

ID | firstname 
---+----------
 1 | alice
 3 | jane
Improve this answer

3 Comments

but does it match my parameter with all the results from the sub-query?
Yes, if your sub-query returns 6 rows 1,6,2,6,11,56, then all your table records with any of those param values will be returned.
As with RaymondHettinger's answer, this is equivalent to ANY, not ALL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.