A subquery is a SELECT query nested inside another SQL query. Subqueries can be part of SELECT/ INSERT/ UPDATE/ DELETE/ MERGE statements.

(SELECT * FROM Dept);

    DEPTNO DNAME          LOC
---------- -------------- -------------
    10     ACCOUNTING     NEW YORK
    20     RESEARCH       DALLAS
    30     SALES          CHICAGO
    40     OPERATIONS     BOSTON

According to Oracle architecture any SELECT statement within brackets is a subquery. What’s the proof?

An ORDER BY clause can’t be used in a Subquery. Oracle reports “ORA-00907: missing right parenthesis” error if you put ORDER BY clause in a Subquery.

--Normal SELECT statements can have ORDER BY clause

SELECT * FROM Dept
ORDER BY Dname;

    DEPTNO DNAME          LOC
---------- -------------- -------------
    10     ACCOUNTING     NEW YORK
    40     OPERATIONS     BOSTON
    20     RESEARCH       DALLAS
    30     SALES          CHICAGO

--Error! ORDER BY clause is not allowed inside a subquery

(
  SELECT * FROM Dept
  ORDER BY Dname
);
  ORDER BY Dname
  *
ERROR at line 3:
ORA-00907: missing right parenthesis

You can have ORDER BY clause outside of a subquery. Watch..

(
  SELECT * FROM Dept
)
ORDER BY Dname;

    DEPTNO DNAME          LOC
---------- -------------- -------------
    10     ACCOUNTING     NEW YORK
    40     OPERATIONS     BOSTON
    20     RESEARCH       DALLAS
    30     SALES          CHICAGO

You can nest subquery in a main query FROM clause.

SELECT *
  FROM
     (
       SELECT * FROM Dept
     )
ORDER BY Dname;

    DEPTNO DNAME          LOC
---------- -------------- -------------
    10     ACCOUNTING     NEW YORK
    40     OPERATIONS     BOSTON
    20     RESEARCH       DALLAS
    30     SALES          CHICAGO

Types of Subqueries

Subqueries can be of following types.

• Single Column Subquery
  • Single Row Single Column Subquery
  • Multiple Row Single Column Subquery
• Multiple Column Subquery
  • Single Row Single Multiple Column Subquery
  • Multiple Row Multiple Column Subquery