Menu
Even though It has a performance issue, may I know what is the use of correlated subquery?
280
The subquery is correlated because the number that it produces depends on main. ship_date, a value that the outer SELECT produces. Thus, the subquery must be re-executed for every row that the outer query considers. The query uses the COUNT function to return a value to the main query.
Subqueries can be categorized into two types: A noncorrelated (simple) subquery obtains its results independently of its containing (outer) statement. A correlated subquery requires values from its outer query in order to execute.
It has only one operand, which is a subquery (correlated or not). If the subquery returns at least one record, then EXISTS returns TRUE . If the subquery returns no records, EXISTS returns FALSE . In this case, you must use a correlated subquery to get your results.
Using EXISTS with a Correlated SubqueryEXISTS operator can be used in correlated subqueries also. Using EXISTS the following query display the employee_id, manager_id, first_name and last_name of those employees who manage other employees.
One common usage example: display details of the latest hired employee(s) for each department:
select e.deptno, e.empno, e.ename, e.hiredate, e.sal
from emp e
where e.hiredate = (select max(e2.hiredate)
from emp e2
where e2.deptno = e.deptno -- the correlation
);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
