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