[Oracle] – subquery: the difference between associated subquery and non associated subquery

Time:2020-10-11

Associated subquery and non associated subquery

1. Associated subquery

  • explain

    For each row of data in the outer query, the internal query will be queried again.

<br/>

  • characteristic

    1. Subqueries cannot be run alone and are related to external queries

    2. The outer query is executed first, and then the inner query

<br/>

  • Examples

    In the following example, the outer query field (t.deptno) is used in the subquery and cannot be run alone (because the outer query field is used)

select t.*, t.rowid  from dept t where exists (select 1 from emp e where e.deptno = t.deptno); 

<br/><br/>

2. Non associated subquery

  • explain

    The subquery first queries the value and then returns it to the outer query.

<br/>

  • characteristic

    1. Subqueries can be run separately

    2. The inner layer query is executed first, and then the outer layer query is executed

<br/>

  • Examples

    As shown below, the statement of subquery can be executed separately — “select max (e.deptno) from EMP E

select t.*, t.rowid  from dept t where t.deptno = (select max(e.deptno) from emp e); 

<br/>