ANY and ALL Operator in SQL.
EXISTS Operator in SQL.
SELECT Emp_name,Salary from Employee WHERE NOT EXISTS (SELECT * from dept_table where Employee.dept_id=dept_table.dept_id);
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
Post a Comment