Subqueries in Oracle

Subqueries are select statements which are part of main select statement.

Subqueries can be used in below clauses of a SELECT statement.

WHERE
HAVING
FROM
ORDER BY

Subqueries are of two types:

Single row subqueries
Multiple row subqueries

Usages of Subqueries:

Subqueries in WHERE clause:

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30;

Subqueries in Oracle: Output — Subqueries in Oracle : output

Using GROUP function in subqueries:

We want to view the list of employees of DEPARTMENT_ID 30 whose salary is greater than that of EMPLOYEE_ID 117 which is 2800.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
AND SALARY > (SELECT SALARY
              FROM EMP_SELECT
              WHERE EMPLOYEE_ID = 117);

We want to view the list of employees of DEPARTMENT_ID 30 whose salary is greater than the average salary of JOB_ID PU_CLERK.

SELECT ROUND(AVG(SALARY), 0) AVG_SALARY 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
AND JOB_ID = 'PU_CLERK';

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
AND SALARY > (SELECT ROUND(AVG(SALARY), 0)
              FROM EMP_SELECT
              WHERE DEPARTMENT_ID = 30
              AND JOB_ID = 'PU_CLERK');

We can check that all the above employee's salary is greater than the average salary 2483 of JOB_ID PU_CLERK.

Subqueries in HAVING clause:

We want to view the JOB_ID wise average salary for DEPARTMENT_ID 50, where average salary is less than the departmental average salary.

JOB_ID wise average salary for DEPARTMENT_ID 50:

SELECT DEPARTMENT_ID, JOB_ID, ROUND(AVG(SALARY), 0) AVG_SALARY 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 50
GROUP BY DEPARTMENT_ID, JOB_ID

Departmental average salary for DEPARTMENT_ID 50:

SELECT ROUND(AVG(SALARY), 0) DEPARTMENTAL_AVG_SALARY
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 50;

JOB_ID wise average salary where average salary is less than the departmental average salary:

SELECT DEPARTMENT_ID, JOB_ID, ROUND(AVG(SALARY), 0) AVG_SALARY 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 50
GROUP BY DEPARTMENT_ID, JOB_ID
HAVING ROUND(AVG(SALARY), 0) < (SELECT ROUND(AVG(SALARY), 0)
                                FROM EMP_SELECT
                                WHERE DEPARTMENT_ID = 50);

In the above output both the average salaries are less than 3476.

We want to view the JOB_ID wise minimum salary for DEPARTMENT_ID 50, where minimum salary is less than the departmental average salary.

SELECT DEPARTMENT_ID, JOB_ID, MIN(SALARY) MIN_SALARY 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 50
GROUP BY DEPARTMENT_ID, JOB_ID

Departmental average salary is 3476.

SELECT DEPARTMENT_ID, JOB_ID, MIN(SALARY) MIN_SALARY 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 50
GROUP BY DEPARTMENT_ID, JOB_ID
HAVING MIN(SALARY) < (SELECT ROUND(AVG(SALARY), 0)
                      FROM EMP_SELECT
                      WHERE DEPARTMENT_ID = 50);

In the above example both the minimum salaries are less than 3476.

Subqueries in FROM clause:

We want to view the DEPARTMENT_ID, JOB_ID wise minimum salary for DEPARMENT_ID 30 and 50.

SELECT DEPARTMENT_ID, 
       JOB_ID, 
       MIN_SALARY
FROM (SELECT DEPARTMENT_ID, 
             JOB_ID, 
             MIN(SALARY) MIN_SALARY 
      FROM EMP_SELECT
      GROUP BY DEPARTMENT_ID, JOB_ID
      )
WHERE DEPARTMENT_ID IN (30, 50)
ORDER BY DEPARTMENT_ID;

We want to view the employee details and department details for DEPARTMENT_ID 30.

SELECT A.EMPLOYEE_ID, 
       A.FIRST_NAME, 
       A.LAST_NAME, 
       A.JOB_ID, 
       A.HIRE_DATE, 
       A.SALARY, 
       A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME,
       B.LOCATION_ID
FROM EMP_SELECT A,
     (SELECT DEPARTMENT_ID,
             DEPARTMENT_NAME,
             LOCATION_ID
      FROM DEPARTMENTS
     ) B
WHERE A.DEPARTMENT_ID = 30
AND A.DEPARTMENT_ID = B.DEPARTMENT_ID
ORDER BY A.EMPLOYEE_ID;

Subqueries in ORDER BY clause:

We want to view the employee details for DEPARTMENT_ID 30 and 50 order by DEPARTMENT_NAME.

SELECT DEPARTMENT_ID, 
       DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID IN (30, 90)
ORDER BY DEPARTMENT_NAME;

We can check that while sorting the department data DEPARTMENT_NAME wise DEPARTMENT_ID 90 will come first.

SELECT A.EMPLOYEE_ID, 
       A.FIRST_NAME, 
       A.LAST_NAME, 
       A.JOB_ID, 
       A.HIRE_DATE, 
       A.SALARY, 
       A.DEPARTMENT_ID  
FROM EMP_SELECT A
WHERE A.DEPARTMENT_ID IN (30, 90)
ORDER BY (SELECT DEPARTMENT_NAME
          FROM DEPARTMENTS
          WHERE DEPARTMENT_ID = A.DEPARTMENT_ID);

Types of Subqueries:

Single row subqueries:

Single row subqueries return only one row from the inner select statement.

Single row operators are:

Operators	Description
=	Equals To
>	Greater Than
>=	Greater Than or Equals To
<	Less Than
<=	Less Than or Equals To
<>	Not Equals To

Subqueries in Oracle: Single row operators

Equals To Operator (=):

We want to view the employee details where DEPARTMENT_ID is that of EMPLOYEE_ID 102.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE EMPLOYEE_ID = 102;

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                       FROM EMP_SELECT
                       WHERE EMPLOYEE_ID = 102);

Greater Than (>):

We want to view the employee details of DEPARTMENT_ID 30 where salary is greater than that of EMPLOYEE_ID 117.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE EMPLOYEE_ID = 117;

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
AND SALARY > (SELECT SALARY
              FROM EMP_SELECT
              WHERE EMPLOYEE_ID = 117);

All the above employee's salary is grater than 2800.

Greater Than or Equals To (>=):

We want to view the employee details of DEPARTMENT_ID 30 where salary is greater than or quals to that of EMPLOYEE_ID 117.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
AND SALARY >= (SELECT SALARY
               FROM EMP_SELECT
               WHERE EMPLOYEE_ID = 117);

All the above employee's salary is grater than or equals to 2800.

Less Than (<):

We want to view the employee details of DEPARTMENT_ID 30 where salary is less than that of EMPLOYEE_ID 117.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
AND SALARY < (SELECT SALARY
              FROM EMP_SELECT
              WHERE EMPLOYEE_ID = 117);

All the above employee's salary is less than 2800.

Less Than or Equals To (<=):

We want to view the employee details of DEPARTMENT_ID 30 where salary is less than or quals to that of EMPLOYEE_ID 117.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
AND SALARY <= (SELECT SALARY
               FROM EMP_SELECT
               WHERE EMPLOYEE_ID = 117);

All the above employee's salary is less than or equals to 2800.

Not Equals To (<>):

We want to view the employee details of DEPARTMENT_ID 60 where salary is not quals to 4800.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 60;

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 60;
AND SALARY <> (SELECT SALARY
               FROM EMP_SELECT
               WHERE EMPLOYEE_ID = 105);

All the above employee's salary is not equals to 4800.

Multiple row subqueries:

Multiple row subqueries return more than one row from the inner select statement.

Multiple row operators are:

Operators	Description
IN	Equals to any member in the subquery
ANY	It compares the value with each value returns by the subquery
ALL	It compares the value with maximum or minimum value returns by the subquery

Subqueries in Oracle : Multiple row operators

IN:

We want to view the employee details where DEPARTMENT_ID is that of EMPLOYEE_ID 102 and 117.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                        FROM EMP_SELECT
                        WHERE EMPLOYEE_ID IN (102, 117));

ANY:

Modified data of DEPARTMENT_ID 30.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY JOB_ID;

We want to view the employee details of DEPARTMENT_ID 30 and JOB_ID PU_MAN whose salary is greater than any salary of JOB_ID PU_CLERK.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
AND SALARY > ANY(SELECT SALARY
                 FROM EMP_SELECT
                 WHERE DEPARTMENT_ID = 30
                 AND JOB_ID = 'PU_CLERK')
AND JOB_ID <> 'PU_CLERK';

We want to view the employee details of DEPARTMENT_ID 30 and JOB_ID PU_MAN whose salary is less than any salary of JOB_ID PU_CLERK.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
AND SALARY < ANY(SELECT SALARY
                 FROM EMP_SELECT
                 WHERE DEPARTMENT_ID = 30
                 AND JOB_ID = 'PU_CLERK')
AND JOB_ID <> 'PU_CLERK';

ALL:

We want to view the employee details of DEPARTMENT_ID 30 and JOB_ID PU_MAN whose salary is greater than the maximum salary of JOB_ID PU_CLERK.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
AND SALARY > ALL(SELECT SALARY
                 FROM EMP_SELECT
                 WHERE DEPARTMENT_ID = 30
                 AND JOB_ID = 'PU_CLERK')
AND JOB_ID <> 'PU_CLERK';

The maximum salary of JOB_ID PU_CLERK is 3100. That's why above row is returned by the query.

We want to view the employee details of DEPARTMENT_ID 30 and JOB_ID PU_MAN whose salary is less than the minimum salary of JOB_ID PU_CLERK.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
AND SALARY < ALL(SELECT SALARY
                 FROM EMP_SELECT
                 WHERE DEPARTMENT_ID = 30
                 AND JOB_ID = 'PU_CLERK')
AND JOB_ID <> 'PU_CLERK';

Based on DEPARTMENT_ID 30 data, the minimum salary of JOB_ID PU_MAN is 2300 which is greater than that of JOB_ID PU_CLERK 2000. That's why the above query does not return any row.

RELATED TOPICS:

SQL Single Row Functions
SQL Group Functions

sayan_bappa@yahoo.co.in

View all posts

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

Subqueries in Oracle