Question: 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.
Any SQL statement
is declarative and any given query can be written in many ways. They will all have the same results, but with different execution speeds.
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 subquery.
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
);
SELECT
USERNAME
FROM
dba_users
WHERE
USERNAME NOT IN
(SELECT
GRANTEE
FROM
dba_role_privs
)
The body of the select statement simply selects the username from dba_users. In the WHERE clause, we select username where there is not a matching row in the dba_role_privs table. Non-correlated subqueries are almost always used with NOT IN predicates in the WHERE clause. It is never a good idea to perform a non-correlated subquery with the IN clause because a regular table join is more efficient.
SELECT
USERNAME
FROM
dba_users
WHERE
USERNAME IN
(SELECT
GRANTEE
FROM
dba_role_privs
);
To illustrate, let us reverse the NOT IN condition from the prior query and change it to an IN Clause. Now we see the following identical queries below.
SELECT
USERNAME
FROM
dba_users a,
dba_role_privs b
WHERE
a.USERNAME = b.GRANTEE;
As we can see, the second query is easier to understand, and it will run much faster than the non-correlated subquery.
Oracle DBA Cloud