Understanding ALL ANY or SOME in SQL
For Complete YouTube Video: Click Here
Here we will try to discuss Understanding ALL, ANY or SOME in SQL.
Table of Contents
Understanding ALL ANY or SOME in SQL
Definitions of ALL, ANY, or SOME: ALL, ANY, or SOME are keywords. These keywords are used with ‘WHERE’ or HAVING Clause.
ALL, ANY, or SOME operate on the subqueries that return multiple values. ALL returns true if all subquery values meet the condition. ANY or SOME returns true if any of the subquery values meet the condition.
To understand this let’s consider the employee table of the company database
Understanding ALL Keyword:
Now consider the query with the use of ALL ” SELECT fname FROM employee WHERE salary ALL (20000, 25000, 30000); “.
What do the above query states?
The query is going to find the fname of all the employees whose salary is greater than ALL these values 20000, 25000, and 30000.
How this query works is it takes the salary value in each and every row and is compared with all the values given (20000, 25000, 30000).
If the salary in the row is greater than ALL the values then the fnames will be displayed.
Understanding ANY or SOME Keyword:
Consider the same query used with ANY ” SELECT fname FROM employee WHERE salary ANY (20000, 25000, 30000); “.
What do the above query states?
The query is going to find the fname of all the employees whose salary is greater than ANY of these values 20000, 25000, and 30000.
How this query works is it takes the salary value in each and every row and is compared with all the values given (20000, 25000, 30000).
If the salary in the row is greater than ANY of the values then the fnames will be displayed.
In place of we can use SOME also.
Example Query on ALL, ANY, or SOME:
Find the fnames and lnames of all the employees whose salary is greater than all the employees in department number 5.
In order to execute this query, we have to find the salaries of all the employees in department number 5. Then those values are to be compared with every employee’s salary.
The answer to the query is ” SELECT fname, lname FROM employee WHERE salary ALL (SELECT salary FROM employee WHERE dno = 5); “.
The output of the inner query is the salaries of the employees who are working in department number 5 (30000, 40000, 38000, 25000).
Now every employee’s salary is compared with ALL the employee’s salary of department number 5.
If the value of the salary is true for all the values then those fname and lname are considered.