Non-correlated Subquery

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