PL/SQL Derived Tables

Derived Tables in PL/SQL are temporary result sets that are created within the execution of a SQL statement. These are essential tools that allow developers to create temporary result sets within SQL statements. This feature enables complex queries to be simplified and enhances readability by encapsulating subqueries.

This feature makes it easier to manage data operations. In this article, we will explain what derived tables are, and their key features, and provide practical examples demonstrating their use in PL/SQL.

What are Derived Tables?

A derived table is a subquery that functions as a temporary table for the outer PL/SQL statement. It enables data aggregation, filtering, and organization before further processing. Derived tables are typically defined in the FROM clause of a query, which allows users to perform various operations such as joins, filtering, and ordering.

Key Features of Derived Tables

• Encapsulation: Derived tables encapsulate logic within the query, making it easier to manage.
  
• Reduced Complexity: They simplify complex queries by breaking them into manageable parts.
  
• Temporary Scope: The derived table exists only for the duration of the query execution and is not stored in the database.

Syntax:

SELECT 
    columns
FROM 
    (SELECT 
        columns
     FROM 
        table_name
     WHERE 
        conditions) AS derived_table_alias

Example 1: Using a Derived Table for Aggregation

In this example, we will demonstrate how to use a derived table to aggregate sales data before performing additional filtering. This approach allows us to calculate summary statistics, such as total quantities sold per product, in a structured manner

Step 1: Create Sample Table and Insert Data

In this step, we will create a sample table named sales that will store information about product sales, including the sale ID, product name, quantity sold, and sale date. After creating the table, we will insert several records into it to establish a dataset that we can use for further analysis.

Query:

CREATE TABLE sales (
    sale_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(50),
    quantity NUMBER,
    sale_date DATE
);

INSERT INTO sales (sale_id, product_name, quantity, sale_date) VALUES (1, 'Product A', 10, DATE '2024-01-15');
INSERT INTO sales (sale_id, product_name, quantity, sale_date) VALUES (2, 'Product B', 20, DATE '2024-01-16');
INSERT INTO sales (sale_id, product_name, quantity, sale_date) VALUES (3, 'Product A', 15, DATE '2024-01-17');
INSERT INTO sales (sale_id, product_name, quantity, sale_date) VALUES (4, 'Product C', 5, DATE '2024-01-18');

Output

Step 2: Using a Derived Table to Filter Aggregated Results

Now, we will use a derived table to calculate the total quantity sold for each product and then filter for products with a total quantity greater than 10.

Query:

SELECT *
FROM (
    SELECT 
        product_name,
        SUM(quantity) AS total_quantity
    FROM 
        sales
    GROUP BY 
        product_name
) AS sales_summary
WHERE 
    total_quantity > 10;

Output

Explanation:

In this example, we created a sales table containing sales records and inserted multiple records for different products and their quantities. After that we utilized a derived table in the second query to aggregate the total quantity sold for each product using the SUM function.

Example 2: Joining a Derived Table with Another Table

In this example, we will demonstrate how to create a derived table that aggregates sales data and then join it with a customers table to retrieve additional information about customers who purchased a specific product.

Step 1: Create a Customer Table and Insert Data

First, we need to create a sample customers table to store customer information. After defining the table structure, we will insert a few sample records representing different customers

Query:

CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    customer_name VARCHAR2(50)
);

INSERT INTO customers (customer_id, customer_name) VALUES (1, 'Customer A');
INSERT INTO customers (customer_id, customer_name) VALUES (2, 'Customer B');

Output

Step 2: Use a Derived Table to Join with Customers

Next, we will create a derived table that calculates the total quantity sold for each product and then join it with the customers table. We will filter the results to focus on a specific product, Product A, to get insights into its sales.

Query:

SELECT 
    c.customer_name,
    s.product_name,
    s.total_quantity
FROM 
    customers c
JOIN (
    SELECT 
        product_name,
        SUM(quantity) AS total_quantity
    FROM 
        sales
    GROUP BY 
        product_name
) AS s ON s.product_name = 'Product A';

Output

Explanation

In this example, we created a customer's table containing customer records and inserted two sample customers. We then used a derived table to aggregate the total quantity sold for each product. We joined the customers table with this derived table on the condition that the product name matches Product A.

Conclusion

In this article, we explained PL/SQL Derived Tables, highlighting their significance in simplifying complex PL/SQL queries. Derived tables allow for temporary result sets that can be utilized in subsequent PL/SQL operations, making it easier to manage and manipulate data.

By encapsulating subqueries within the FROM clause, we can enhance the readability and maintainability of our PL/SQL statements. Using derived tables in our PL/SQL queries can significantly enhance our ability to aggregate, filter, and organize data efficiently.