Nesting of queries, one within the other is a subquery.
A statement containing a subquery is called a parent query.
Subqueries are used to retrieve data from tables that depend on the values in the table itself.
SQL> select * from emp where sal > (select sal from emp where empno = 7566);
---It issingle row subquery, it will return one value.
SQL> select * from emp where sal > any (select sal from emp where sal between 2500 and 4000);
..In multi row subquery, it will return more than one value.
SQL> select * from emp where sal = (select max(sal) from emp where sal < (select max(sal) from emp));
..it is multiple sub queries.There is no limit on the number of subqueries included in a where clause. It allows nesting of a query within a subquery.
SQL> select distinct deptno from emp e where 5 <= (select count(ename) from emp where e.deptno = deptno);
...it is correlated subqueries