SQL ALL and ANY Operators

ALL & ANY are logical operators. They are used with a WHERE or HAVING caluse. They return boolean value as a result.

The ALL operator returns TRUE if all of the subquery values satisfy the condition.

The ANY operator returns TRUE if any of the subqueries satisfy the condition.

Syntax

• ALL Syntax

  The syntax for the ALL operator is as follows:

  SELECT column_list
  FROM table_name
  WHERE column_name operator ANY (subquery)
  

• ANY Syntax

  The syntax for the ANY operator is as follows:

  SELECT column_list
  FROM table_name 
  WHERE column_name operator ALL (subquery)
  

Parameters:

• colmun_list: The names of columns you want to select.
• operator: It is the standard comparison operator ( =, >, >=, <, <=, <>, != ).
• subquery: This is a SELECT statement whose result set will be checked against the specified expression.

Demo Table

Let us the following "Client" and "Order" tables:

• Client table:

  +----+------------+-----------+-------------+---------+
  | id | first_name | last_name | city        | country |
  +----+------------+-----------+-------------+---------+
  | 1  | Maria      | Martinez  | Boston      | USA     |
  | 2  | Donna      | Williams  | Seattle     | USA     |
  | 3  | James      | Smith     | Los Angeles | USA     |
  | 4  | Jennifer   | Garcia    | Las Vegas   | USA     |
  | 5  | Stephanie  | Miller    | Seattle     | USA     |
  | 6  | Robert     | Hernandez | Houston     | USA     |
  | 7  | Andrew     | Rodriguez | Detroit     | USA     |
  +----+------------+-----------+-------------+---------+
  

• Order table:

  +----+-----------+------------+------------+----------+
  | id | client_id | order_date | product_id | quantity |
  +----+-----------+------------+------------+----------+
  | 1  | 3         | 2018-05-26 | 26         | 4        |
  | 2  | 5         | 2018-10-01 | 15         | 2        |
  | 3  | 3         | 2018-10-06 | 19         | 1        |
  | 4  | 7         | 2018-11-12 | 33         | 3        |
  | 5  | 6         | 2018-12-16 | 7          | 2        |
  +----+-----------+------------+------------+----------+
  

SQL ALL Example

The ALL operator returns TRUE if all of the subquery values satisfy the condition.

The following SQL statement will return TRUE and lists all the clients if all the rows in the "Order" table have quantity = 2.

SELECT *
FROM client 
WHERE client_id = ALL (SELECT client_id FROM order WHERE quantity = 2);

After the execution, the above statement will output the following result set:

+----+------------+-----------+-------------+---------+
| id | first_name | last_name | city        | country |
+----+------------+-----------+-------------+---------+
|    |            |           |             |         |
+----+------------+-----------+-------------+---------+

As we can see above, the result set is empty because not ALL rows in the Order table have quantity = 2.

SQL ANY Example

The ANY operator returns TRUE if any of the subqueries satisfy the condition.

The following SQL statement will return TRUE and list the clients if there are any rows in the "Order" table with quantity = 2.

SELECT *
FROM client 
WHERE client_id = ANY (SELECT client_id FROM order WHERE quantity = 2);

After the execution, the output will be as follows:

+----+------------+-----------+-------------+---------+
| id | first_name | last_name | city        | country |
+----+------------+-----------+-------------+---------+
| 1  | Maria      | Martinez  | Boston      | USA     |
| 2  | Donna      | Williams  | Seattle     | USA     |
| 3  | James      | Smith     | Los Angeles | USA     |
| 4  | Jennifer   | Garcia    | Las Vegas   | USA     |
| 5  | Stephanie  | Miller    | Seattle     | USA     |
| 6  | Robert     | Hernandez | Houston     | USA     |
| 7  | Andrew     | Rodriguez | Detroit     | USA     |
+----+------------+-----------+-------------+---------+

As we can see, the above output display all clients because there is at least one row in the "Order" table that has quantity = 2.