What is Nested Select Statement in PL/SQL?
Last Updated :
08 Feb, 2024
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 TableAfter inserting data into the grades table, The table looks:
Grades TableIn 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:
OutputExplanation: 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.
Similar Reads
SQL Tutorial Structured Query Language (SQL) is the standard language used to interact with relational databases. Whether you want to create, delete, update or read data, SQL provides the structure and commands to perform these operations. SQL is widely supported across various database systems like MySQL, Oracl
8 min read
Non-linear Components In electrical circuits, Non-linear Components are electronic devices that need an external power source to operate actively. Non-Linear Components are those that are changed with respect to the voltage and current. Elements that do not follow ohm's law are called Non-linear Components. Non-linear Co
11 min read
SQL Commands | DDL, DQL, DML, DCL and TCL Commands SQL commands are crucial for managing databases effectively. These commands are divided into categories such as Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Data Query Language (DQL), and Transaction Control Language (TCL). In this article, we will e
7 min read
Spring Boot Tutorial Spring Boot is a Java framework that makes it easier to create and run Java applications. It simplifies the configuration and setup process, allowing developers to focus more on writing code for their applications. This Spring Boot Tutorial is a comprehensive guide that covers both basic and advance
10 min read
Normal Forms in DBMS In the world of database management, Normal Forms are important for ensuring that data is structured logically, reducing redundancy, and maintaining data integrity. When working with databases, especially relational databases, it is critical to follow normalization techniques that help to eliminate
7 min read
Class Diagram | Unified Modeling Language (UML) A UML class diagram is a visual tool that represents the structure of a system by showing its classes, attributes, methods, and the relationships between them. It helps everyone involved in a projectâlike developers and designersâunderstand how the system is organized and how its components interact
12 min read
SQL Query Interview Questions SQL or Structured Query Language, is the standard language for managing and manipulating relational databases such as MySQL, Oracle, and PostgreSQL. It serves as a powerful tool for efficiently handling data whether retrieving specific data points, performing complex analysis, or modifying database
15+ min read
Backpropagation in Neural Network Back Propagation is also known as "Backward Propagation of Errors" is a method used to train neural network . Its goal is to reduce the difference between the modelâs predicted output and the actual output by adjusting the weights and biases in the network.It works iteratively to adjust weights and
9 min read
3-Phase Inverter An inverter is a fundamental electrical device designed primarily for the conversion of direct current into alternating current . This versatile device , also known as a variable frequency drive , plays a vital role in a wide range of applications , including variable frequency drives and high power
13 min read
Polymorphism in Java Polymorphism in Java is one of the core concepts in object-oriented programming (OOP) that allows objects to behave differently based on their specific class type. The word polymorphism means having many forms, and it comes from the Greek words poly (many) and morph (forms), this means one entity ca
7 min read