Skip to main content

ANY, ALL, EXISTS Operators in SQL Query

ANY and ALL Operator in SQL.
                                    ANY is a special logical operator and is used along with comparison operator. it return true if any of the subquery  value will satisfied by outer query. 
                                    ALL is a special logical operator and is used along with comparison operator. it return true if all of the subquery  value will satisfied by outer query. 

Employee
 id     Emp_name      Age         Salary Dept_id
 1 Jebas  35 80000 2
 2             Thomas  30 40000 1
 3 Mark              25 40000 2
 4         Harry  42 62500 2
 5 Willam  45 125000 1
 6 James  32 35000 3
 7 Mock  22 15000 3

SELECT Emp_name,Salary from Employee WHERE salary> ANY(SELECT salary from Employee where dept_id=2);
This will display the employee salary that was greater than any one of salary return by subquery. The subquery return salary of id =2 , ie 40000 and 62500. The outer query will return.
Output:
Employee
 Emp_name     Salary
 Jebas 80000
 Harry 62500
 Willam 125000

SELECT Emp_name,Salary from Employee WHERE salary> ALL(SELECT salary from Employee where dept_id=2);
This will display the employee salary that was greater than all of the salary return by subquery. The subquery return salary of id =2 , ie 40000 and 62500. The outer will return.
Output.
Employee
 Emp_name     Salary
 Willam 125000

EXISTS Operator in SQL.
                    It used to check the existence of record in subquery.
Employee
 id     Emp_name      Age         Salary Dept_id
 1 Jebas  35 80000 2
 2             Thomas  30 40000 1
 3 Mark              25 40000 2
 4         Harry  42 62500 2
 5 Willam  45 125000 1
 6 James  32 35000 3
 7 Mock  22 15000 3
 8 Don Meon  41 78000 NULL    
Dept_table
 Dept_id Dept_name
     1 Director
     2 Manager
     3 Employee
     4 HR
     5 TeamLeader
     6 Developer
     7 Tester

SELECT Emp_name,Salary from Employee WHERE NOT EXISTS (SELECT * from dept_table  where Employee.dept_id=dept_table.dept_id);
The display the name of employee not belongs any department.
Employee
 Emp_name     Salary
 Don Meon 78000

SELECT dept_name from  dept_table
 WHERE EXISTS (SELECT * from Employee where Employee.dept_id=dept_table.dept_id and Employee.age >40);
This query check the existence of department whose employee age greater than 40
Dept_table
 Dept_name
 Director
 Manager


Thank You. Like Share and Subscribe 👍 folowlect.com

Comments