SQL ANY Operator – A Beginner’s Guide
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
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.
