Use Of Correlated Subquery

Even though It has a performance issue, may I 开发者_Go百科know what is the use of correlated subquery?

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
                    );

Well, firstly it doesn't have a performance issue. It is what it is, and it will be executed as well as possible given the performance constraints of the hardware and database structure.

As for what it is useful for, it is just a way of expressing particular logical conditions.

A correlated subquery is used some action must be taken on each row in your query that depends on one or more values from that row.

So, for instance, if you want to know include a row in a result set based on something happening in another table, you might do:

SELECT * FROM YourTable YT WHERE 
EXISTS (SELECT * FROM SomeOtherTable SOT WHERE
    SOT.ID = YT.ID AND SOT.SomeInteger BETWEEN YT.LowInteger AND YT.HighInteger)

Similarly in an UPDATE:

UPDATE YourTable YT SET YourColumn = 
  (SELECT SUM(SomeColumn) FROM SomeOtherTable SOT 
      WHERE SOT.ID = YT.ID AND SOT.SomeField <> YT.SomeField)

Sometimes these queries can be written with standard JOINs, but sometimes not.