Associated subquery and non associated subquery
1. Associated subquery
For each row of data in the outer query, the internal query will be queried again.
1. Subqueries cannot be run alone and are related to external queries
2. The outer query is executed first, and then the inner query
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);
2. Non associated subquery
The subquery first queries the value and then returns it to the outer query.
1. Subqueries can be run separately
2. The inner layer query is executed first, and then the outer layer query is executed
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);