Introduction

In this article, we are going to see how the ANY operator works in SQL using it with a subquery or a VALUES clause, as well as its NULL value handling behavior.

Database tables

Let’s assume we have two database tables that form a one-to-many table relationship:

The student table is the parent, and it contains the following two records:

| id | first_name | last_name | admission_score |
|----|------------|-----------|-----------------|
| 1  | Alice      | Smith     | 8.95            |
| 2  | Bob        | Johnson   | 8.75            |

And, the student_grade table is the child, and it contains the following rows:

| id | class_name | grade | student_id |
|----|------------|-------|-----------|
| 1  | Math       | 10    | 1         |
| 2  | Math       | 9.5   | 1         |
| 3  | Math       | 9.75  | 1         |
| 4  | Science    | 9.5   | 1         |
| 5  | Science    | 9     | 1         |
| 6  | Science    | 9.25  | 1         |
| 7  | Math       | 8.5   | 2         |
| 8  | Math       | 9.5   | 2         |
| 9  | Math       | 9     | 2         |
| 10 | Science    | 10    | 2         |
| 11 | Science    | 9.4   | 2         |

SQL ANY Operator

If we want to get all students with the admission score greater than at least one class grade, then we could use the following SQL query:

SELECT 
    id, 
    first_name, 
    last_name, 
    admission_score
FROM 
    student
WHERE 
    admission_score > ANY (
        SELECT 
            student_grade.grade
        FROM 
            student_grade
        WHERE
            student_grade.student_id = student.id
    )
ORDER BY id

The outer query filters the student table rows using the ANY operator, which returns true if the admission_score is greater than at least one class grade value returned by the underlying subquery. Otherwise, the ANY operator returns false, meaning that the admission_score is lower than all the student class grades.

When executing the above SQL query, we get the following result set:

| id | first_name | last_name | admission_score |
|----|------------|-----------|-----------------|
| 2  | Bob        | Johnson   | 8.75            |

Because Bob has an 8.5 grade in Math and his admission score was 8.75, the ANY operator returns true, and Bob’s record is included in the result set. On the other hand, all Alice’s class grades are greater than her admission score, so the ANY operator will return false, so the outer query WHERE clause will not include Alice’s row in the final result set.

SQL ANY Operator NULL Value Handling

To see how the ANY operator behaves when the subquery returns a NULL value, consider the following SQL query:

SELECT 
    id, 
    first_name, 
    last_name, 
    admission_score
FROM 
    student
WHERE 
    admission_score > ANY (
        VALUES 
            (8), 
            (null)
    )
ORDER BY id

The VALUES clause allows us to define a virtual table via a comma-separated list of rows. In our case, the virtual table has a single column and two rows.

When executing the above SQL query, we can see that it returns both our students:

| id | first_name | last_name | admission_score |
|----|------------|-----------|-----------------|
| 1  | Alice      | Smith     | 8.95            |
| 2  | Bob        | Johnson   | 8.75            |

The NULL value is ignored because the previous query is equivalent to the following SQL query:

SELECT
    id,
    first_name,
    last_name,
    admission_score
FROM
    student
WHERE
    admission_score > 8 OR
    admission_score > NULL
ORDER BY id

The admission_score > NULL is evaluated to NULL. But, because of the OR operator, if the first condition returns true, then the WHERE clause will return true as well. And, since both students have the admission score greater than 8, then both students will be included in the outer query result set.

Just like with the OR conditional operator, the ANY operator allows the database Optimizer to break early the execution (e.g., short-circuit) upon getting the first true condition evaluation, meaning that the left-side and right-side values match the provided comparison operator.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

The ANY operator takes the left-hand value and compares it using the provided operator (e.g., <, >, =) against all values provided by the right-hand subquery or expression. If at least one left-hand value to right-hand value comparison returns true, then the ANY operator will return true. Otherwise, the ANY operator returns false.

The ANY operator is equivalent to SOME, so you can use both forms interchangeably.

Insert details about how the information is going to be processed

DOWNLOAD NOW