Co-related sub query and nested sub query in SQL

Question

What is co-related sub query and nested sub query in SQL.

Akkiraju Ivaturi · Answer

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 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.