PL/SQL Subqueries

PL/SQL subqueries are powerful SQL features that allow for the nesting of one query inside another for dynamic data retrieval. They have extensive applications when complex problems are to be solved by reducing them into smaller, more manageable queries.

The inner query, usually called the subquery, returns results that the outer or main query uses for filtering, comparison, or further processing. Subqueries can be applied to every part of the SQL statement: SELECT, FROM, or even inside a WHERE clause.

PL/SQL Subqueries

PL/SQL subqueries let you include one query inside another to make data retrieval more flexible. The inner query runs first and provides results that the outer query uses for filtering or processing.

This is helpful when solving complex problems because you can break them into smaller, easier steps. Subqueries can be used in different parts of an SQL statement, like in the SELECT or WHERE clauses, to help manage and analyze data more effectively.

Syntax:

SELECT column_name
FROM table_name
WHERE column_name = (SELECT column_name FROM another_table WHERE condition);

Key Terms:

• SELECT column_name: This part of the query selects data from a column in table_name.
  
• FROM table_name: Specifies the table from which the data will be selected.
  
• WHERE column_name =: This filters the rows in table_name based on the condition that follows.

Examples of PL/SQL Subqueries

PL/SQL subqueries are powerful tools that allow queries to be nested inside other queries, making data retrieval more dynamic and flexible. By breaking down complex operations into smaller, manageable parts, subqueries enhance efficiency and enable more detailed data analysis.

These subqueries can be used in various parts of an SQL statement, including the SELECT, FROM, and WHERE clauses, each offering unique functionalities to handle complex tasks.

Departments Table

The departments table is created with three columns: department_id, department_name, and location_id. The department_id is the primary key, and five departments with respective locations are inserted into this table.

Query:

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50),
    location_id INT
);

INSERT INTO departments (department_id, department_name, location_id)
VALUES
    (1, 'HR', 100),
    (2, 'IT', 200),
    (3, 'Finance', 100),
    (4, 'Marketing', 300),
    (5, 'Operations', 200);

Output:

Explanation:

The table contains information about five departments: HR, IT, Finance, Marketing, and Operations, each with a unique department_id and location_id.

Employees Table

The employees table is created with columns like employee_id, employee_name, department_id, and salary. The department_id is a foreign key that references the departments table. Nine employees are inserted into this table, each belonging to one of the five departments.

Query:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO employees (employee_id, employee_name, department_id, salary)
VALUES
    (1, 'John Doe', 1, 5000),
    (2, 'Jane Smith', 2, 7000),
    (3, 'Sam Brown', 3, 6000),
    (4, 'Lisa White', 1, 5500),
    (5, 'Tom Johnson', 4, 7500),
    (6, 'Nancy Allen', 2, 7200),
    (7, 'Steve Adams', 5, 6300),
    (8, 'Mike Taylor', 3, 5800),
    (9, 'Sophie King', 1, 5200);

Output:

Explanation:

The table now holds data for nine employees, including their employee_id, employee_name, associated department_id, and their salary. Each employee belongs to a department, and their salary varies based on their role.

Types of Subqueries

SQL Subqueries-One of the main types of subqueries, depending on what for and how they connect with the principal query, is shown below. The main types of subqueries are as follows:

1. Single-Row Subquery

The query retrieves the employee_name from the employees table where the department_id matches any of the department_id values from the subquery. The subquery finds all departments located at location_id = 100 from the departments table.

Query:

SELECT employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 100);

Output:

Explanation:

The result displays "Alice" as the employee working in a department located at location_id = 100. The subquery first identifies the relevant department_id, and the outer query filters employees working in those departments.

2. Multiple-Row Subquery

• The query retrieves the employee_id, employee_name, and salary of employees who work in departments located at location_id = 200.
  
• The subquery first fetches the department_id values from the departments table for those located at location_id = 200 (IT and Operations).
  
• The outer query then filters the employees table for employees working in these departments.

Query:

SELECT employee_id, employee_name, salary
FROM employees
WHERE department_id IN (
  SELECT department_id
  FROM departments
  WHERE location_id = 200
);

Output:

Explanation:

• This question lists employees who work in departments located at location_id = 200. This query uses a subquery to identify first the department_id values of departments in that location by searching the departments table for location_id = 200.
  
• This time the subquery identifies that the IT and Operations departments are in this location. The main query then uses this result to filter the employees table, returning the employee_id, employee_name, and salary for all employees who belong to those departments.
  
• As a result, the employees Jane Smith, Nancy Allen, and Steve Adams would appear in the output. This helps decompose complex conditions into smaller queries.

3. Correlated Subquery

In that case, the subquery will compute the average salary for each department coming from the outer query. It constitutes a correlated subquery because it draws its input from the department_id in the outer query.

Such subqueries are dependent upon values from the main query for their operation.

Query:

SELECT employee_name
FROM employees emp
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = emp.department_id);

Output:

Explanation:

The employees "Bob" and "David" are displayed because their salaries are higher than the average salary of their respective departments. Each department's average salary is calculated dynamically based on the department_id from the outer query.

4. Nested Subquery

A nested subquery is a subquery included within another subquery. In other words, actually doing several layers of querying. In other words, it is a query inside a query inside another query.

Query:

SELECT employee_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE location_id = (SELECT location_id FROM locations WHERE city = 'New York');

Output:

Explanation:

The employee "Alice" is displayed because she works in a department located in New York. The query runs through multiple nested layers to extract this information.

Scalar Subquery

A scalar subquery returns precisely one value-one row, and one column. It can be used in any part of a query where a single value is expected. This subquery retrieves the name of the department that each employee belongs to.

Query:

SELECT employee_name,
(SELECT department_name FROM departments WHERE department_id = employees.department_id) AS department_name
FROM employees;

Output:

Explanation:

The result shows each employee's name along with their department. For example, "Alice" belongs to the HR department, and "Bob" works in the Finance department. Each employee has one corresponding department name returned by the scalar subquery.

Subqueries with SELECT, FROM, and WHERE Clauses

Subqueries can be used in various parts of an SQL statement: the SELECT, FROM, or WHERE clauses, each serving a unique purpose. When used in the SELECT clause, subqueries return computed or derived values to enhance the result set. Subqueries in the FROM clause act as inline views, creating temporary tables for further operations.

In the WHERE clause, subqueries help filter the results based on conditions from another table or query. Each type of subquery allows for complex data retrieval and manipulation, enabling more dynamic and detailed analysis of data.

Subqueries in the SELECT Clause

The subqueries in the SELECT clause return some derived or calculated value that appears in the results of the main query. It will be generally used when you need to get some extra data requiring computation or a lookup.

Query:

SELECT employee_name, 
       (SELECT department_name 
        FROM departments 
        WHERE department_id = employees.department_id) AS department_name
FROM employees;

Output:

Explanation:

The result displays each employee’s name along with their department. For example, "John" and "Alice" are shown as being part of the "Sales" department, while "Jane" is in the "IT" department. The subquery retrieves the department name dynamically for each employee.

Subqueries in the FROM Clause

Subqueries that occur in the FROM clause are also called inline views. An inline view is a temporary result table produced by an inner query that is nested inside an outer query.

Lets use the employees table again. Now, we want to calculate the average salary per department and join back to the original table in order to get employees' names and the average salary that their corresponding department earns.

Query:

SELECT e.employee_name, avg_salary
FROM employees e,
     (SELECT department_id, AVG(salary) AS avg_salary 
      FROM employees 
      GROUP BY department_id) avg_dept
WHERE e.department_id = avg_dept.department_id;

Output:

Explanation:

The result shows the names of employees, such as "John," "Jane," and "Alice," along with the average salary for their department, which is 6000 in this case. The subquery groups salaries by department_id and calculates the average salary, joining it with employee data in the main query.

Subqueries with WHERE Clause

This query retrieves the employee_name from the employees table where the department_id matches the department_id of the "Sales" department, found using a subquery in the WHERE clause.

Query:

SELECT employee_name
FROM employees
WHERE department_id = (SELECT department_id 
                       FROM departments 
                       WHERE department_name = 'Sales');

Output:

Explanation:

The result lists "John" and "Alice" as employees in the "Sales" department. The subquery finds the department_id for "Sales" in the departments table, and the main query uses this to filter the employees working in that department.

Conclusion

In conclusion, PL/SQL subqueries are an essential tool for writing both efficient and flexible SQL. They allow the developer to deal with complicated data retrieval tasks by allowing one query to depend on the results of another.

Knowing when to use a subquery versus a join, and being able to use different types of subqueries-correlated, multi-row-will greatly enhance both performance and readability of your SQL code. Subqueries let you carry out more effective database operations and simplify the handling of relational data.