SQL - Sub Query

A subquery also know as nested query. Its a query with another sql query and embedded within the WHERE clause To retrieve data used in main query as a condition to further restrict the data. A subquery can contain another subquery. No limit on the number of subquery levels in the from clause of the top-level.

  • Subquery
  • Single row subquery
  • Multiple row subquery
  • Multiple column subquery.
  • Co-related subquery.
  • Nested subquery.

    SINGLE ROW SUBQUERY :
    It returns zero or one row. 
        postgres=# select job from emp where deptno=
    (select deptno from dept where dname='RESEARCH');

    job
    ---------
    CLERK
    MANAGER
    ANALYST
    CLERK
    ANALYST
    (5 rows)

    Operators
    =
    >
    >=
    <
    <=
    <>
    MULTIPLE ROW SUBQUERY :
    Returns more than one row 
        postgres=# select ename from emp where job in
    (select job from emp where deptno=20);
    ename
    --------
    SMITH
    JONES
    BLAKE
    CLARK
    SCOTT
    ADAMS
    JAMES
    FORD
    MILLER

    Operators
    In
    Not in
    Any
    All
    Exists
    MULTIPLE COLUM SUBQUERY :
    It returns one or more columns 
        postgres=# select e.ename, d.dname, e.sal
    from emp e, dept d
    where e.deptno = d.deptno
    and (sal, coalesce(comm,0)) in
    (select sal, coalesce(comm,0) from emp e, dept d
    where e.deptno=d.deptno
    and d.dname='RESEARCH');
    ename |  dname   | sal
    -------+----------+------
    SMITH | RESEARCH |  800
    JONES | RESEARCH | 2975
    SCOTT | RESEARCH | 3000
    ADAMS | RESEARCH | 1100
    FORD  | RESEARCH | 3000
    CO-RELATED SUBQUERY :
    It references one or more columns in the outer SQL Statement. The subquery is known as a correlated subquery because the subquery is related to the outer SQL Statement. 
        postgres=# select e.ename, e.sal, e.deptno,(select avg(sal) from emp
    where deptno=e.deptno) as "average" from emp e
    where deptno < 30 order by 3;
    ename  | sal  | deptno |        average
    --------+------+--------+-----------------------
    MILLER | 1300 |     10 | 2916.6666666666666667
    CLARK  | 2450 |     10 | 2916.6666666666666667
    KING   | 5000 |     10 | 2916.6666666666666667
    SCOTT  | 3000 |     20 | 2175.0000000000000000
    SMITH  |  800 |     20 | 2175.0000000000000000
    ADAMS  | 1100 |     20 | 2175.0000000000000000
    JONES  | 2975 |     20 | 2175.0000000000000000
    FORD   | 3000 |     20 | 2175.0000000000000000
    NESTED SUBQUERY :
    Subqueries are placed within another subquery. 
        postgres=# select ename, job from emp where ename in
    (select ename from emp where deptno=
    (select deptno from dept where dname='RESEARCH'));
    ename |   job
    -------+---------
    SMITH | CLERK
    JONES | MANAGER
    FORD  | ANALYST
    SCOTT | ANALYST
    ADAMS | CLERK
    SUBQUERY WITH FUNCTION : 
        postgres=# select ename from emp where sal=
        (select max(sal)from emp);

    ename
    -------
    KING


    ☛ Join to Learn from Experts: PostgreSQL SQL Course in Chennai by TesDBAcademy
    (Postgres - Views)