Subqueries in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Subqueries in Oracle with Examples

In this article, I am going to discuss Subqueries in Oracle with Examples. At the end of this article, you will understand what are subqueries, why we need subqueries, and how to use subqueries in Oracle with Examples.

What is a subquery in Oracle?

In Oracle, a subquery is a query within a query. We can create subqueries within our SQL statements. These subqueries can reside in the WHERE clause, in the FROM clause, or in the SELECT clause.

Example to understand Oracle Subquery

We are going to use the following Employee table to understand Subquery in Oracle with Examples.

Please use the below SQL Script to create and populate the Employee and Dept table with the required sample data.

CREATE TABLE EMPLOYEE
(	
        EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2)
);

INSERT INTO EMPLOYEE VALUES (7369, 'SMITH', 'CLERK',      7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7499, 'ALLEN',  'SALESMAN',  7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMPLOYEE VALUES (7521, 'WARD', 'SALESMAN',    7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMPLOYEE VALUES (7566, 'JONES',  'MANAGER',   7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7654, 'MARTIN', 'SALESMAN',  7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMPLOYEE VALUES (7698, 'BLAKE',  'MANAGER',   7839, TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMPLOYEE VALUES (7782, 'CLARK',  'MANAGER',   7839, TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMPLOYEE VALUES (7788, 'SCOTT',  'ANALYST',   7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7839, 'KING',   'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMPLOYEE VALUES (7844, 'TURNER', 'SALESMAN',  7698, TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30);
INSERT INTO EMPLOYEE VALUES (7876, 'ADAMS',  'CLERK',     7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7900, 'JAMES',  'CLERK',     7698, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMPLOYEE VALUES (7902, 'FORD',   'ANALYST',   7566, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7934, 'MILLER', 'CLERK',     7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

Example:

The following query uses the MAX() aggregate function to return the highest salary from the Employee table.
SELECT MAX( SAL ) FROM EMPLOYEE;
Output:

To select the detailed information of the highest-paid employee, we use the highest Salary (5000) in the following query.
SELECT * FROM EMPLOYEE WHERE SAL = 5000;
Output:

As you can notice, we need to execute two queries separately to get the highest-paid employee information from the Employee table. By using a subquery, we can nest the first query inside the second one as shown in the following statement.
SELECT * FROM EMPLOYEE WHERE SAL = (SELECT MAX( SAL ) FROM EMPLOYEE);
Output:

In the above example, the query that retrieves the max salary is called the subquery, and the query that selects the detailed employee data is called the outer query. We can say that the subquery or inner query is nested within the outer query. Note that a subquery must appear within parentheses (). Oracle evaluates the whole query in two steps:

1. First, execute the subquery.
2. Second, use the result of the subquery in the outer query.

Note: A subquery that is nested within the FROM clause of the SELECT statement is called an inline view. A subquery nested in the WHERE clause of the SELECT statement is called a nested subquery.

Subqueries in Oracle

A query inside another query is called a subquery or nested query in Oracle. The subquery is having two more queries. those are as follows,

1. Inner / Child / Sub query
2. Outer / Parent / Main query

Syntax: SELECT * FROM <TN> WHERE <CONDITION> (SELECT * FROM ………………);

As per the execution process of the subquery, it is again classified into two categorized.

1. Non-Correlated Subqueries: In non-correlated subqueries, first, the inner query will execute and return value, and later outer query will execute.
2. Correlated Subqueries: In Correlated subqueries first outer query will execute and return value and later inner query will execute.

The Non-Correlated Subqueries are again classified into 4 types are as follows.

1. Single Row Subquery
2. Multiple Row Subquery
3. Multiple Column Subquery
4. Inline View Subquery

Advantages of Oracle Subqueries

The following are the main advantages of subqueries:

1. Provide an alternative way to query data that would require complex joins and unions.
2. Make the complex queries more readable.
3. Allow a complex query to be structured in a way that it is possible to isolate each part.

In the next article, I am going to discuss Single Row Subquery in Oracle with examples. Here, in this article, I try to explain Subqueries in Oracle with Examples. I hope you enjoy this Oracle Subqueries article.