CODEWITHSUNDEEP
mysqlsql
besnico
besnico

Reputation: 271

Can I use '= ALL' in SQL Query?

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

Upvotes: 4

Views: 14142

Answers (3)

Justin Niessner
Justin Niessner

Reputation: 245399

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

Upvotes: 4

Guffa
Guffa

Reputation: 700252

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.

Upvotes: 3

Cylindric
Cylindric

Reputation: 5894

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

Upvotes: -1

Related Questions