Hi, i am trying to understand something about correlated sql sub-queries. the following is taked from some web tutorial. regarding the third example, they say that
since only the dept_id is returned by the sub query, there is no need for any subsequent table access. For these reasons, the third query would normally perform better than the first.
thats looks incorrect to me... the third example looks very correlated subquery to me, so it should perform slowlier than the first.
Is there something i do not underst here???
Example 1 - non-correlated sub query SELECT dept.name FROM dept
WHERE dept.id NOT IN
( SELECT dept_id
FROM emp
WHERE dept_id IS NOT NULL)
Example 2- outer join SELECT dept.name FROM dept,emp
WHERE emp.dept_id (+) = dept.id
Example 3 - correlated sub query SELECT dept.name FROM dept
WHERE NOT EXISTS (SELECT dept_id
FROM emp
WHERE emp.dept_id = dept.id)
The second example is an outer join SQL statement. This in fact does more than just return the names of departments which have no employees assigned to them, it also returns the names of those departments that do have employees assigned to them. This is because an outer join returns both matching rows and the non-matching rows on one side of the join.
The first and the third SQL statements would produce exactly the same results, but the first would probably be slower than the third if the dept_id column in the emp table were indexed (depending on the sizes of the tables).
The first SQL statement can not use any indexes - the where clause of the sub query is just checking for NOT NULL rows - so a full table scan would be performed. Also the sub query would be executed once for each row in the dept table.
On the other hand, the sub query in the third example can use the index and since only the dept_id is returned by the sub query, there is no need for any subsequent table access. For these reasons, the third query would normally perform better than the first.