VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
= compares a value to 1 value. ANY and ALL put a comparison in front of a
whole subquery result: greater than any of them, greater than all of them. You will meet them more often in other people’s queries than write them
yourself, so the thing to get from this lesson is being able to read one.
Open the client with mysql -u root -p sakila before you start.
How ANY and ALL work
COLUMN_LIST with the columns you want, TABLE_NAME with the table,
COLUMN_NAME with the column to test, and OTHER_TABLE with the table the
subquery reads. Replace > with any comparison operator.
ANY is true when the comparison holds against at least 1 returned row. ALL
is true when it holds against every one. SOME is another spelling of ANY
and means exactly the same thing.
Both give NULL rather than true or false when a NULL in the subquery leaves the
answer undecided, the same way any other comparison against NULL does, and
WHERE then drops the row.
Read them as the plain English: > ANY is “bigger than at least one of them”,
which is the same as bigger than the smallest. > ALL is “bigger than every
one of them”, the same as bigger than the largest.
Examples
1) Bigger than at least one
G film runs 47 minutes, so every film of 48 or more qualifies.
2) Bigger than every one
G film is 185 minutes,
which is also the longest film in the catalog, so nothing is longer than all
of them.
3) = ANY is IN
= ANY and IN are the same operator written 2 ways, and IN
is the one people read faster. <> ALL is likewise NOT IN, with the same
NULL trap that lesson describes.
An empty subquery flips them
This is the part worth knowing, because it looks wrong.X, so the subquery returns nothing, and every film comes
back. ALL over an empty set is true: there is no row that fails the test.
ANY over an empty set is false for the mirror reason, so the same query with
ANY returns nothing.
A typo in the subquery therefore gives you the whole table with ALL and an
empty result with ANY, in both cases without an error.
Summary
- Read
> ANYas bigger than the smallest, and> ALLas bigger than the largest. - Read
SOMEas another spelling ofANY. - Prefer
INover= ANYandNOT INover<> ALL, because they read faster. - Expect
ALLto be true andANYto be false when the subquery returns nothing. - Expect NULL rather than true or false when a NULL in the subquery leaves it undecided.
See also
- IN — the same test, written the way most people write it
- Subqueries — the queries these operators compare against
- EXISTS — testing for a row rather than comparing values