Co-related sub query and nested sub query in SQL

A subquery is independent of outer query.

For example,

select * from employees where employeeid in (select employeeid from employees where
empmanager='john')

here first select employeeid from employees table where empmanager='john' is executed based on the resultant values the outer query is executed.

In simple words, a correlated subquery has two parts. Outer query and sub query. Sub query uses data from outer query or references one or more columns of the outer query.

For example,

select distinct orderid from orderdetails od
where orderquantity < (select avg(orderquantity)*0.1 from orderdetails where
od.productid = productid)

Here outer query has an alias name od and the subquery is using the alias to refer the column productid "od.productid".

nested subquery is where a subquery depends gets value from other subquery and then outer query is performed on the basis of top subquery

For example,

select empid from employees where employeeid in (select employeeid from employee where empmanager = (select empmanager from employee where mgrid = 56))

here first select empmanager from employee where mgrid=56 is executed then based on its output results the top subquery is executed and then the outer query is executed.