MySQL - ANY and ALL

In this tutorial we will learn about ANY and ALL operators in MySQL.

The ANY operator

The ANY operator returns TRUE if the comparison is TRUE for ANY of the values returned by the subquery.

ANY syntax

SELECT column_name
FROM table_name
WHERE
  some_column comparison_operator ANY (
    SELECT some_other_column
    FROM some_other_table
    WHERE some_other_condition
  )

Where comparison_operator is any of the comparison operator like =, <, >, <=, >=, !=, <>.

The ALL operator

The ALL operator returns TRUE if the comparison is TRUE for ALL of the values returned by the subquery.

ANY syntax

SELECT column_name
FROM table_name
WHERE
  some_column comparison_operator ALL (
    SELECT some_other_column
    FROM some_other_table
    WHERE some_other_condition
  )

Tables

In this tutorial we will use the employee and comments table.

Table: employee

mysql> SELECT * FROM employee;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e01        | Yusuf     | Shakeel  | yusuf@example.com    |     7 | 1900-01-01 | 2018-02-04 06:08:10 | 2018-01-01 01:01:01 |
| e02        | John      | Doe      | johndoe@example.com  |     8 | 1900-02-03 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e03        | Jane      | Doe      | janedoe@example.com  |     9 | 1900-05-20 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e04        | Tin       | Tin      | tintin@example.com   |     6 | 1900-10-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 |
| e05        | Bob       | Coder    | bobcoder@example.com |     7 | 1900-08-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)

Table: comments

mysql> SELECT * FROM comments;
+-----------+------------+---------------------+---------------------+---------------------+
| commentid | employeeid | commentbody         | lastmodified        | created             |
+-----------+------------+---------------------+---------------------+---------------------+
|         1 | e03        | Awesome             | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
|         2 | e03        | Hello World         | 2018-01-02 01:02:03 | 2018-01-02 01:02:03 |
|         3 | e01        | Happy               | 2018-01-02 01:04:03 | 2018-01-02 01:04:03 |
|         4 | e04        | This is my comment. | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
|         5 | e01        | Keep coding....     | 2018-04-04 03:02:01 | 2018-05-04 03:02:01 |
+-----------+------------+---------------------+---------------------+---------------------+
5 rows in set (0.00 sec)

Example: ANY operator

In the following example we will show the detail of those employees who commented at least twice.

mysql> SELECT 
    e.employeeid, e.firstname, e.lastname
FROM
    employee e
WHERE
    e.employeeid = ANY (SELECT
            c.employeeid
        FROM
            comments c
        GROUP BY c.employeeid
        HAVING COUNT(c.commentid) >= 2);

+------------+-----------+----------+
| employeeid | firstname | lastname |
+------------+-----------+----------+
| e01        | Yusuf     | Shakeel  |
| e03        | Jane      | Doe      |
+------------+-----------+----------+
2 rows in set (0.00 sec)

Explanation:

The above query lists the detail of the employees if any of the c.employeeid returned by the subquery is equal to the e.employeeid of the employee table and the c.employeeid has commented at least twice.

The subquery returns the following values.

mysql> SELECT
    c.employeeid
FROM
    comments c
GROUP BY c.employeeid
HAVING COUNT(c.commentid) >= 2;

+------------+
| employeeid |
+------------+
| e01        |
| e03        |
+------------+
2 rows in set (0.00 sec)

So, our query becomes the following.

mysql> SELECT 
    e.employeeid, e.firstname, e.lastname
FROM
    employee e
WHERE
    e.employeeid = ANY ('e01', 'e03');

This means if e.employeeid is equal to ANY of the two values of the subquery then the detail of that employee is listed.

Example: ALL operator

In the following example we will list details of the employees only if they never commented.

mysql> SELECT 
    e.employeeid, e.firstname, e.lastname
FROM
    employee e
WHERE
    e.employeeid != ALL (SELECT DISTINCT
            c.employeeid
        FROM
            comments c);

+------------+-----------+----------+
| employeeid | firstname | lastname |
+------------+-----------+----------+
| e02        | John      | Doe      |
| e05        | Bob       | Coder    |
+------------+-----------+----------+
2 rows in set (0.01 sec)

The above query gives details of the employeeid 'e02' and 'e05' who never commented.

Explanation:

We get the following output for the given subquery.

mysql> SELECT DISTINCT
    c.employeeid
FROM
    comments c;

+------------+
| employeeid |
+------------+
| e01        |
| e03        |
| e04        |
+------------+
3 rows in set (0.00 sec)

So, employeeid 'e01', 'e03' and 'e04' commented at least once.

Substituting the above values we will get the following query.

mysql> SELECT 
    e.employeeid, e.firstname, e.lastname
FROM
    employee e
WHERE
    e.employeeid != ALL ('e01', 'e03', 'e05');

So, the above query returns detail of those employees whose employeeid is not 'e01', 'e03' and 'e05'.