SUBQUERY in Oracle with Examples

A Subquery is a SELECT statement which is used inside other SELECT statement. It can also be used inside INSERT, UPDATE or DELETE statements. A Subquery can be placed inside either WHERE, FROM or HAVING clause after SELECT statement.

Syntax:

SELECT Column1, Column2, Column_n ...
FROM table_name
WHERE sub-query-with-condition;

Let’s see one query using Employees Table to understand how subquery works:

Suppose, we want to find a list of employees who are earning more than the employee whose employee_id = 145.

For this we need two queries:

1. First, to find the salary of employee whose employee_id =145
2. Second, to find the list of employees who are earning more than that.

We will use the result of first query in second query to get final result. Here are the two queries we need to execute:

--Query for finding salary of employee with employee_id=145

SELECT salary
FROM employees
WHERE employee_id = 145;

--Gives result = 14000

--Query for finding employees who are earning more than 14000

SELECT *
FROM employees
WHERE salary > 14000;

Using a Subquery is a quicker way that allows us to club these two queries together. We can get the final result with just one query. Have a look:

SELECT *
FROM employees
WHERE salary > (SELECT salary
FROM employees
WHERE employee_id = 145);

Result:

This query returns a list of 3 employees who are earning more than the salary of employee with employee_id=145.

In this case case, the query that returns the salary of employee with employee_id =145 is the subquery. The subquery needs to be in the parenthesis ( ) and always gets executed first before the outer query.

Some Points to keep in mind regarding Subqueries:

1. A subquery can contain another subquery. Oracle allows us multiple levels for nesting subqueries.
2. If the subquery comes after FROM clause of SELECT statement then it is called an Inline View.
3. If the subquery is nested in the WHERE clause of SELECT statement then it is called as Nested Subquery.

Using a Subquery is a much easier and powerful way of retrieving information from tables and to reduce the number of steps it takes to get the final result.

In this tutorial we will learn about the usage of Single Line and Multi-Line Subqueries with the help of these examples:

Oracle Subquery Examples

1. Single Row Subquery Example

When the nested subquery or the inner query inside parenthesis returns just one row then it is an example of Single Row Subquery.

Suppose, we want to find the employees who are earning more than the highest earning employee in department=50, then we use this query:

SELECT first_name,last_name, salary
FROM employees
WHERE salary >
( SELECT MAX(salary)
FROM employees
WHERE department_id=50);

Result:

The query returns the name and salary of all employees who are earning more than the highest earning employee in department-50. Oracle first executes the nested subquery and returns 8200 as a result and then the outer query finds all employees who are earning more than that.

We also used an aggregate or group function( MAX) in the nested subquery in this example.

2. Multiple Row Subquery Example

If the nested subquery returns more than one rows then it is an example of Multiple Row Subquery.

Let’s look at an example where we try to find employees whose salary is same as salary of any employee in department = 30. The IN operator determines whether a value matches any values in a list or a subquery.

SELECT first_name, last_name, salary
FROM employees
WHERE salary IN (SELECT salary
FROM employees
WHERE department_id = 30);

Result:

The Oracle first executes the nested subquery to find salary of all employees in department-30. There are total 6 employees in department-30 and their salary can be seen below:

The Outer query returns all the employees whose salary matches with salary of employees in department-30.

When the inner nested subquery returns more than one row, then usual comparison operators ( =, <, > …) must be used with ANY or ALL. Here’s what these operators denote:

1. ANY – If used with comparison operator, the final outcome will be true if operator evaluates to TRUE for ANY values that a subquery returns.
2. ALL – If used with comparison operator, the outcome will be true only if operator evaluates to TRUE for ALL values that a subquery returns.

Let’s understand this through one query:

Suppose we want to find list of employees whose salary is ‘greater than any’ value of salary in department-30, then we use this query:

--Greater than minimum

SELECT first_name, last_name, salary
FROM employees
WHERE salary > ANY (SELECT salary
FROM employees
WHERE department_id = 30);

Result:

The query returns all employees whose salary is more than the salary of lowest earning employee in department-30.

Similarly, we can use comparison operators with ANY and ALL operators to get different outcomes:

Employees whose salary is:

--Greater than minimum ( salary of employee in dept-30)

SELECT first_name, last_name, salary
FROM employees
WHERE salary > ANY (SELECT salary
FROM employees
WHERE department_id = 30);

--Less than maximum ( salary of employee in dept-30)

SELECT first_name, last_name, salary
FROM employees
WHERE salary < ANY (SELECT salary
FROM employees
WHERE department_id = 30);

--Greater than maximum ( salary of employee in dept-30)

SELECT first_name, last_name, salary
FROM employees
WHERE salary > ALL (SELECT salary
FROM employees
WHERE department_id = 30);

--Less than minimum ( salary of employee in dept-30)

SELECT first_name, last_name, salary
FROM employees
WHERE salary < ALL (SELECT salary
FROM employees
WHERE department_id = 30);

This might seem a bit confusing at first glance, but you just need to practice on your own to get a good grasp on this concept.

3. Subquery in FROM clause

When we use a subquery in FROM clause of SELECT, then it is called as an inline view.

We use the query for joining 3 tables:

SELECT *
FROM locations a ,
( SELECT region_id, country_id, country_name,
region_name
FROM countries NATURAL JOIN regions ) b
WHERE a.country_id = b.country_id

Result:

The Inline View or Subquery is used for joining ‘Countries’ and ‘ Regions’ table, while the outer query joins the output of inline view with ‘Locations’ table. We get a join of 3 tables as the final output.

In this tutorial, we learned how to use Subquery in Oracle in different scenarios.