Oracle SQL 9i Introduction and OCA guide...CLICK HERE !!!


Subquery in Oracle

A Subquery is a query within a query. It is also known as inner query.

In this article will discuss the following :

Subquery example and important points -

Single row subquery -

Multiple row subquery -

Subquery in a FROM/WHERE/HAVING clause -



Subquery example and important points

Look at the following statements –

SELECT MAX (SALARY) FROM EMP_DETAILS
WHERE SALARY < (SELECT MAX (SALARY) FROM EMP_DETAILS);

In this example we are trying to find out the second highest salary from the table named EMP.  Instead of specifying any value in the WHERE clause, we are using another query. This is the inner query. This query will be executed first and its value will be compared with in outer query.

Points to note –

  • A subquery can be written in the WHERE clause, FROM clause or in HAVING clause.
  • A subquery is also known as inner query. The main query is known as the outer query
  • A subquery is written within parenthesis.
  • There are two types of subqueries – Single Row Subquery & Multiple Row Subquery.

Single Row Subquery

A single row subquery will return a single row. The example above is a single row subquery - SELECT MAX (SAL) FROM EMP will return only one row.  In single row subqueries, we use operators like =, >, <, <>, >=, <= etc.

Below are some examples of single row subqueries –

SELECT * FROM EMP_DETAILS
WHERE DEPT_ID = (SELECT DEPTNO FROM DEPT_DETAILS WHERE DNAME = 'HR');

SELECT * FROM STUDENTS
WHERE GRADE > (SELECT GRADE FROM STUDENT_MASTER WHERE STU_ID = 555);

Multiple Row Subquery

A multiple row subquery returns more than one row. IN, ANY, ALL etc can be used in single row subqueries.

Here are some examples –

SELECT * FROM STUDENTS
WHERE DEPARTMENT_ID IN
(SELECT DEPT_ID FROM STUDENT_MASTER WHERE GRADE > 5);

SELECT EMP_NAME, EMP_ID, SALARY FROM EMP_DETAILS
WHERE SALARY > ANY
(SELECT SALARY FROM EMPLOYE_HISTORY WHERE DEPT_NAME = ‘SALES’)

SELECT EMP_NAME, EMP_ID, SALARY FROM EMP_DETAILS
WHERE SALARY > ALL
(SELECT SALARY FROM EMPLOYE_HISTORY WHERE DEPT_NAME = ‘IT’)

Examples: Subquery in a FROM/WHERE/HAVING clause

As discussed above, a subquery can be written in the FROM clause, WHERE clause or HAVING clause of a SQL statement.

Here are some very simple examples for better understanding –

SELECT MAX (SALARY) FROM EMP_DETAILS
WHERE SALARY < (SELECT MAX (SALARY) FROM EMP_DETAILS);


SELECT A.NAME, A.ID, A.JOB, B.LOCATION
FROM EMP_DETAILS A, (SELECT * FROM DEPT_DETAILS) B
WHERE A.DEPT_ID=B.DEPT_ID;


SELECT DEPT_NO, COUNT (*) FROM STUDENTS
GROUP BY DEPT_NO
HAVING COUNT (*) >
(SELECT COUNT (*) FROM STUDENT_HISTORY WHERE DEPT_ID = 200);


SQL Samples PLSQL Samples DBA Samples
Example of SELECT statement Example of plsql Block Example of Controlfile management
Example of INSERT statement Example of Loop Example of Datafile management
Example of UPDATE statement Example of Conditional statement Example of Tablespace management
Example of ALTER statement Example of plsql Records Example of Redo Log management
Example of MERGE statement Example of plsql Cursor Example of RMAN
Example of CASE & DECODE Example of plsql Trigger Example of Incremental backup
Example of CREATE statement Example of plsql Procedure Example of Data pump
Example of Constraint Example of plsql Function Example of Export Import
Example of Join Example of plsql Parameters Example of Partitioning
Example of Delete, Truncate & Drop Example of Varray Example of External table
Example of Subquery Example of Nested Table
Example of Index Example of Associative Array
Example of Synonym
Example of Sequence
Example of View