MySQL ANY and ALL subquery keywords

Databases
1

Hey guys.

I’ve been looking into subqueries and I found two recursive subquery clauses that I’m unfamiliar with.

Does anyone know how ANY () and ALL () work?

I tried something like this:

SELECT s.name FROM students s WHERE id = ANY (SELECT f.owner_id FROM friends f WHERE f.user_id = 11)

This seemed to return all the values (kinda like a join statement).

Where…

SELECT s.name FROM students s WHERE id = ALL (SELECT f.owner_id FROM friends f WHERE f.user_id = 11)

This seemed to return nothing.

I believe that ANY works like a recursive loop for all the subquery results (if its more than one value), but I have no idea how ALL works.

Any ideas?

2

It has nothing to with recursion.

Any means that if there is at least one row in the subquery that fulfils the predicate then the search condition is true.


where id = ANY(select ... )

is the same as


where id in (select

There is no need to use any as it can be rewritten using exists

Analoguously, ALL means that if all rows in the subquery fulfils the predicate (or if the subquery is empty) then the search condition is true.

All can also be rewritten with exists.

3

tack så mycket!