Question: Can you give me some tips on
when to write non-correlated subqueries? Are non-correlated
subqueries faster than a correlated subquery?
Answer: In non-correlated subqueries the subquery
should be executed before the outer query and the non-correlated
subquery is executed only once. The data from the outer query and
the subquery are independent and one execution of the subquery will
work for all the rows from the outer query.
Remember, any SQL statement is "declarative and any given query
can be
written in many ways. They will all
have the same results, but with vastly different execution speeds!
For more details on non-correlated sub queries, see the book
"Advanced Oracle SQL Tuning The Definitive Reference".
In this example of a non-correlated subquery, we locate all
authors who have not yet published a book. Note that the
subquery is non-correlated because the subquery makes no reference
to anything in the outer query.
select
author_last_name
from
author
where
author_key not in
(select author_key from book_author);
This type of NOT IN subquery is called a non-correlated subquery
because the subquery does not make any references to the outside
query. We can sometimes re-write a non-correlated subquery
into a correlated. Here is a non-correlated subquery:
select
stuff
from
tablename
where
key IN
-- noncorrelated subquery
(select
other_stuff
from
inner_table
);
Here is the correlated subquery equivalent. Note the reference in
the inner query to the column value in the outer query:
select
stuff
from
tablename
where
key IN
-- correlated subquery
(select
other_stuff
from
inner_table
where
tablename.key =
inner_table.key
);
