What is the use of EXISTS/ANY/ALL Operators in MySQL
EXISTS/ANY/ALL Operators in MySQL are used along with the where condition to check if the condition added is satisfied.
We will see the use of each Operator below.
Records present in the ‘students’ and ‘students_report’ are shown in the below tables.
- EXISTS Operator
The EXISTS operator is used to check if any record exists in the subquery.
Example for the EXISTS:
First, we will check the records present in the student_report table for roll number = 1.
Now we will get the records from the ‘students’ table using EXISTS for roll number 1 if marks are > 90 and 80 respectively.
From the above image, we know that marks > 90 records are not present hence we should get the empty result for students’ records using this condition.
If the same details we check for marks > 80 then we should get the all results from the ‘students’ table as shown below.
- ANY Operator
If any value matches the range given then the condition will be true and the respective records will b returned as shown below.
- ALL Operator
This will check if all the values present in the subquery matches with the condition provided. The condition will be true if the operation is true for all values in the range. In the example below not all the roll numbers present in the ‘student_report’ are present in the ‘students’ table. So if we try the equal condition using the ANY operator it will return false so no results will be returned from the ‘students’ table.
If instead of equals condition if we check for not an equal condition for ALL operator since roll number 6 is not present in the ‘student_report’ table we should get the record for this roll number as shown below.
