Here we will try to discuss Understanding ALL, ANY or SOME in SQL.
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 operates 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 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 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 the department number 5.
If the value of the salary is true for all the values then those fname and lname are considered.
Link for our website: learningmonkey.in
Follow us on Facebook @ / learningmonkey
Follow us on Instagram @ / learningmonkey1
Follow us on Twitter @ / _learningmonkey
Mail us @ learningmonkey01@gmail.com