Subquery is a query within a query i.e. the inner query runs one time and its result is used by the outer query. For e.g.
Select Ename from emp where city = (Select city from CityMaster where cityid=102)
In the above query the inner query runs only one and its result is used by the outer query. But sometimes developers encounter a situation such as:
To find out all the employees who are getting salary greater than the average salary of their own departments. Now in this kind of query, for every outer query the inner query will calculate the average salary of the department and then it is compared with the outer query. So it means the inner query fires for every row in the outer query unlike the normal sub query. This is called as "Co-related SubQuery". The actual query will be:
Select x.ename, x.salary, x.deptno from emp x where x.Salary >
(
Select avg(salary) from emp where deptno=x.deptno
)
Select Ename from emp where city = (Select city from CityMaster where cityid=102)
In the above query the inner query runs only one and its result is used by the outer query. But sometimes developers encounter a situation such as:
To find out all the employees who are getting salary greater than the average salary of their own departments. Now in this kind of query, for every outer query the inner query will calculate the average salary of the department and then it is compared with the outer query. So it means the inner query fires for every row in the outer query unlike the normal sub query. This is called as "Co-related SubQuery". The actual query will be:
Select x.ename, x.salary, x.deptno from emp x where x.Salary >
(
Select avg(salary) from emp where deptno=x.deptno
)
Comments