What is Nested Select Statement in PL/SQL?

PL/SQL is a Procedural Language/Structured Query Language and it enables users to write procedural logic directly within the database, including procedures, functions, triggers, and packages. In this article, we will understand Nested select statements in PL/SQL along with the examples and so on.

Understanding Nested Select Statement in PL/SQL

The nested select statement in PL/SQL is a feature that allows for the retrieval of data from multiple tables in a single query. It involves using a SELECT statement within another SELECT statement to fetch data based on specified conditions.

The main concept of a nested select statement in PL/SQL involves using a SELECT statement within another SELECT statement. This allows for the retrieval of data from multiple tables or views based on specified conditions. The syntax for a nested select statement is as follows:

Syntax:

-- Outer SELECT Statement: Retrieves data from table1 based on conditions

SELECT column1, column2, ...
FROM table1
WHERE condition IN (

    -- Nested SELECT Statement: Retrieves data from table2 based on separate conditions

    SELECT column3, column4, ...
    FROM table2
    WHERE condition
);

Explanation of Syntax:

Outer SELECT Statement:

• The outer SELECT statement start the query execution by specifying the columns (column1, column2, etc.) which we want to retrieve from table1 and generally defines the foundation of the query and indicates the primary data source.

Nested SELECT Statement:

• Nested within the IN clause of the outer SELECT statement.This inner query selects specific columns (column3, column4, etc.) from table2 and operates independently and retrieving data from table2 based on its own set of conditions.

Examples of Nested Select Statement in PL/SQL

Examples 1

Let’s Say we have two tables, “employees” and “departments.” with the Following Data.

We will create the Employees and Departments table which defines the table structure with columns such as emp_id, emp_name, dept_Id and Salary of Employees TABLE and dept_Id, dept_name, and location of departments TABLE which specify the appropriate data types for each column to store information about employees and departments.

Query:

-- Create departments table

CREATE TABLE departments 
(
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50),
    location VARCHAR(50)
);

INSERT INTO departments (dept_id, dept_name, location) VALUES
(1, 'IT', 'New York'),
(2, 'HR', 'London'),
(3, 'Finance', 'New York');

-- Create employees table

CREATE TABLE employees 
(
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

INSERT INTO employees (emp_id, emp_name, dept_id, salary) VALUES
(101, 'John Doe', 1, 60000.00),
(102, 'Jane Smith', 1, 65000.00),
(103, 'Michael Johnson', 2, 55000.00),
(104, 'Emily Brown', 3, 70000.00),
(105, 'David Lee', 1, 62000.00);

After inserting data into the departments and employees table, The table looks:

Departments and Employees tables

In this example we want to retrieve the names of employees along with their corresponding department names. We can achieve this using a nested select statement as shown below:

Query:

SELECT emp_name, 
    (SELECT dept_name 
     FROM departments 
     WHERE departments.dept_id = employees.dept_id) AS dept_name
FROM 
    employees
WHERE 
    dept_id IN (
        SELECT dept_id
        FROM departments
        WHERE location = 'New York'
    );

Output:

Output

Explanation: We can observe that we get the names of employees along with their corresponding department names.

Examples 2

Let’s Say we have two tables Students and Grades with the Following Data.

We can create the Students and Grades table using the following query which defines the table structure with columns such as student_id, and name of Students TABLE and student_id, course, and grade of Grades TABLE which specify the appropriate data types for each column to store information about Students and Grades.

Query:

-- Create students table

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);


INSERT INTO students (student_id, name) VALUES
(1, 'John Doe'),
(2, 'Jane Smith'),
(3, 'Michael Johnson'),
(4, 'Emily Brown');

-- Create grades table

CREATE TABLE grades (
    student_id INT,
    course VARCHAR(100),
    grade CHAR(1)
);


INSERT INTO grades (student_id, course, grade) VALUES
(1, 'Math', 'A'),
(1, 'Science', 'B'),
(2, 'Math', 'B'),
(2, 'Science', 'A'),
(3, 'Math', 'A'),
(3, 'Science', 'A'),
(4, 'Math', 'C'),
(4, 'Science', 'B');

After inserting data into the students table, The table looks:

Students Table

After inserting data into the grades table, The table looks:

Grades Table

In this example We want to retrieve the names of students who have achieved an ‘A‘ grade in any course. We can achieve this using a nested select statement.

Query:

SELECT name
FROM students
WHERE student_id IN (
    SELECT student_id
    FROM grades
    WHERE grade = 'A'
);

Output:

Output

Explanation: We can observe that the names of students who have achieved an ‘A‘ grade in any course.

Conclusion

Overall, the nested select statement in PL/SQL provides a method for retrieving data from multiple tables based on specified conditions. By using single SELECT query within another which allow the user to efficiently perform query and manipulate data across related tables in a single operation. With a good understanding of nested select statements you can easily write more complex and efficient SQL queries.