The SQL ANY and ALL operators allow you to use sub-queries within your WHERE clause to filter your queries. This lesson explains how to use the SQL ANY and ALL statements.
SQL Any
The SQL ANY operator is used inside the WHERE clause to test whether a value matches one or more of the values provided from the subquery. Therefore, it is also referred to as the SQL WHERE ANY operator.
For example, suppose you want to query the first and last names of every customer who made a rental between 2005-05-25 and 2005-05-30 in the MySQL Sakila demo database.
To do this, you could use the following SQL WHERE ANY query:
SELECT first_name, last_name
FROM customer c
WHERE customer_id =
ANY (SELECT customer_id
FROM rental
WHERE rental_date
BETWEEN "2005-05-25" AND "2005-05-30");
By using the SQL ANY query, you can generate a list of every customer whose value in the rental date column is equal to any date between your chosen date range.
SQL All
The SQL ALL operator is used inside the WHERE clause to test whether a value matches all of the values provided from the subquery.
As an example of SQL ALL, you can take the same query as above but just for customers who rented on the 25th.
SELECT first_name, last_name
FROM customer c
WHERE customer_id =
ALL (SELECT customer_id
FROM rental
WHERE rental_date = "2005-05-25");
Thanks to this query, you have a list of every customer whose value in the rental date is equal to May 5th, 2005.
Summary: SQL Where Any + SQL All Operators
- SQL
ANY- aka SQLWHERE ANY- returns values if they match one or more values from the sub-query - The SQL
ALLoperator returns values if they match all values from the sub-query - Both
ANYandALLoperators are placed inside theWHEREclause
SQL Any + SQL All Syntax Here's the syntax for the SQL WHERE ANY and ALL commands, where you can substitute the variables in < > with your values.
-- ANY operator
SELECT <YOUR_COLUMNS>
FROM <YOUR_TABLE>
WHERE <YOUR_COLUMN> =
ANY (SELECT <YOUR_COLUMNS>
FROM <YOUR_TABLE>
WHERE <YOUR_CONDITION>)
-- ALL operator
SELECT <YOUR_COLUMNS>
FROM <YOUR_TABLE>
WHERE <YOUR_COLUMN> =
ALL (SELECT <YOUR_COLUMNS>
FROM <YOUR_TABLE>
WHERE <YOUR_CONDITION>)