Even though It has a performance issue, may I know what is the use of correlated subquery?
+3
A:
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.
David Aldridge
2009-11-25 09:02:16
Why it wont be having performance issue as it firstly finds record in outer query then pass it to inner query and then inner query will will give some result depending upon which again outer query selects expected result....
hrishi
2009-11-25 10:57:33
@hrishi: Because that's not what it does. SQL is declarative, so you write your desired results, not the operations to get to that result. Determining how to retrieve your result is a job for the query optimizer.
erikkallen
2009-11-25 11:06:33
What you're describing there is pretty much a join. The query transformation stage of the optimisation process can convert a correlated subquery to a join and it could be implemented as a hash join or nested loop (etc). A correlated subquery against a very large unindexed table could well be a performance issue but that's because the join is inefficient, not because the syntax for specifying it in SQL implies an inherently ineffcient database operation.
David Aldridge
2009-11-25 11:06:37
+2
A:
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
);
Tony Andrews
2009-11-25 11:02:27
A:
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.
Larry Lustig
2009-11-25 21:32:22