Using a correlated subquery

Question

Despite having a performance problem, can I find out what the use of a correlated subquery is?

Answer 1

Well, firstly, it has no performance problem. This is what it is and it will be executed, and it is also possible given the limitations of hardware performance and database structure.

As for why this is useful, this is just a way of expressing specific logical conditions.

Answer 2

One common use case: displaying information about the last 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 ); 

Answer 3

A correlated subquery is used, in each row of your query some action should be performed, which depends on one or more values ​​from this row.

So, for example, if you want to know, include a row in a result set based on something that happens in another table, you can 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 UPDATE:

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

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