SQL - SUBQUERIES

  1. A subquery also know as nested query
  2. Its a query within another SQL query and embedded within the WHERE clause.
  3. To retrieve data used in main query as a condition to further restrict the data.
  4. A subquery can contain another subquery.
  5. No limit on the number of subquery levels in the FROM clause of the top-level query.
  6. Oracle allow to nest up to 255 levels of subquery in the WHERE clause.
There are five types



Single row subquery:
Return only one row
Use single-row comparison operators

Operator Meaning: 
=	Equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
<>	Not equal to





Multiple row subquery:
  1. Return more than one row
  2. Use multiple-row comparison operators
  3. Operator Meaning
  4. IN equal to any member in the list
  5. ANY compare value to each value returned by the subquery
  6. ALL compare value to every value returned by the subquery

select ename,job,sal from emp where job in (select job from emp where deptno=20);

select ename,job,sal from emp where sal>any (select sal from emp where deptno=20);

select ename,job,sal from emp where sal>all (select sal from emp where deptno=20);

Multiple column subquery :

It returns one or more columns

select ename,job,sal from emp where (job,sal) in (select job,sal from emp where deptno=10);

Co-related subquery :

It reference one or more columns in the outer SQL statement. The subquery is known as acorrelated subquery because the subquery is related to the outer SQL statement.

select a.*,(select avg(sal) from emp where deptno=a.deptno)as "average" from emp a where deptno=30;

Nested subquery:

Two or more Subqueries are placed within another subquery.

select * from emp where ename in(select ename from emp where deptno=(select deptno from dept where dname ='SALES'));
(SQL - Views)