Correlated subquery and non-correlated subquery

Subquery: queries nested in other queries.
A subquery is also called an internal query, while an external query (also called a primary query) contains the subquery statement ).
All subqueries can be divided into two types: correlated subqueries and non-correlated subqueries.
1> non-correlated subqueries are subqueries that are independent from external queries. subqueries are executed once in total and the values are passed to external queries after execution.
2> the execution of related subqueries depends on the data in the external query. If one row is executed in the external query, the subquery is executed once.
Therefore, non-correlated subqueries are more efficient than correlated subqueries.

Query the names of employees whose salaries are higher than the average salaries of all employees:
-- Non-correlated subquery
Select department_id, first_name
From EMP
Where department_id = 110
And salary> (select AVG (salary)
From EMP
Where department_id = 110);-A subquery is an independent query.

Query the names of employees whose salaries are higher than the average salaries of the Department:
-- Related subqueries
Select e1.department _ id, e1.first _ name
From EMP E1
Where salary> (select AVG (salary)
From EMP E2
Where e2.department _ id = e1.department _ id)-dependent on external query results
Order by e1.department _ id;