CROSS APPLY vs INNER JOIN in PL/SQL

PL/SQL Stands for procedural language extension to SQL. In a procedure, the role of the subprogram is to perform a particular task and it is a unit module of a program. It combined to form larger programs. A subprogram can be involved by another program which is called the calling program. PL/SQL provides a block structure of executable unit code. It Provides procedural constructs, for example, in control structure includes loops, conditional statements, and variable, constant, and data type.

In PL/SQL, CROSS APPLY and INNER JOIN serve different purposes and are used in different contexts. It’s important to understand their functionalities and use cases to decide when to use one over the other.

INNER JOIN

• Purpose: An INNER JOIN is used to combine rows from two or more tables based on a related column between them. It returns only the rows where there is a match between the columns specified in the join condition.

• Use Cases:
  • When you want to retrieve data from multiple tables based on a common column.
  • Typically used for equi-joins where the condition involves the equality of columns.

CROSS APPLY

• Purpose: CROSS APPLY is an operator used in SQL Server (it’s not part of PL/SQL, which is used in Oracle). It is primarily used with table-valued functions. It evaluates a table-valued function for each row produced by the outer table expression.

• Use Cases:
  • When you want to invoke a table-valued function for each row of another table expression.
  • Useful when the function returns a set of values for each row and you want to join or cross-apply those values to the outer table.

Example of CROSS APPLY over INNER JOIN

Example 1: Retrieving Latest Salary Using CROSS APPLY

The provided SQL script creates two tables, employees and job_history, and inserts some sample data. The subsequent query utilizes CROSS APPLY to retrieve the latest salary for each employee from the job_history table.

Table:

CREATE TABLE employees (employee_id INT PRIMARY KEY, employee_name VARCHAR(50));
CREATE TABLE job_history (employee_id INT,start_date DATE, salary INT,PRIMARY KEY (employee_id, start_date));

Insert Data Into Table:

INSERT INTO employees VALUES (1, 'John');
INSERT INTO employees VALUES (2, 'Alice');
INSERT INTO employees VALUES (3, 'Bob');

INSERT INTO job_history VALUES (1, '2022-01-01', 50000);
INSERT INTO job_history VALUES (1, '2022-02-01', 55000);
INSERT INTO job_history VALUES (2, '2022-01-01', 60000);
INSERT INTO job_history VALUES (3, '2022-01-01', 70000);

Query Using CROSS APPLY:

SELECT employees.employee_id, employees.employee_name, latest_salary.salary
FROM employees
CROSS APPLY (
    SELECT TOP 1 salary
    FROM job_history
    WHERE job_history.employee_id = employees.employee_id
    ORDER BY start_date DESC
) AS latest_salary;

Output:

Explanation:

In this example, CROSS APPLY clause is used to apply the subquery for each row in the employees table.

• The subquery retrieves the TOP 1 (latest) salary from the job_history table for the corresponding employee, ordered by start_date in descending order.
• The result set includes the employee ID, employee name, and the latest salary for each employee.

Choosing Between INNER JOIN and CROSS APPLY:

• Use INNER JOIN when you want to combine rows based on matching columns in different tables.
• Use CROSS APPLY when you want to apply a table-valued function to each row from another table expression.

Example 2: Color-Animal Relationship Exploration with CROSS APPLY

Table:

CREATE TABLE Color (Id INT, Name VARCHAR(10));
CREATE TABLE Animal(Id INT, ReferenceId INT, Name VARCHAR(10));

Insert Data into table:

INSERT INTO Color(Id,Name) VALUES (1, 'Red');
INSERT INTO Color(Id,Name) VALUES (2, 'Green');
INSERT INTO Color(Id,Name) VALUES (3, 'Blue');
INSERT INTO Color(Id,Name) VALUES (4, 'Yellow');
INSERT INTO Color(Id,Name) VALUES (5, 'Purple');


INSERT INTO Animal(Id, ReferenceId,Name) VALUES (1, 1, 'Dog');
INSERT INTO Animal(Id, ReferenceId,Name) VALUES (2, 1, 'Cat');
INSERT INTO Animal(Id, ReferenceId,Name) VALUES (3, 2, 'Bird');
INSERT INTO Animal(Id, ReferenceId,Name) VALUES (4, 4, 'Horse');
INSERT INTO Animal(Id, ReferenceId,Name) VALUES (5, 3, 'Bear');
INSERT INTO Animal(Id, ReferenceId,Name) VALUES (6, 1, 'Deer');

Query Using CROSS APPLY:

SELECT C.Name,
       A.Name
FROM Color C
    CROSS APPLY
(SELECT Name FROM Animal A WHERE A.ReferenceId = C.Id) A;

Output:

Explanation:

The FROM Color C part retrieves data from the Color table, aliasing it as C.

The LEFT JOIN Animal A ON C.Id = A.ReferenceId specifies a left join between the Color and Animal tables based on the condition that the Id from the Color table matches the ReferenceId from the Animal table.

The SELECT C.Name AS ColorName, A.Name AS AnimalName selects the Name column from the Color table (aliased as C) and the Name column from the Animal table (aliased as A). The use of AS is optional; it is used to provide a clear alias for each column in the result set.

The query retrieves color names and, where available, the corresponding animal names based on the ReferenceId relationship. If there is no matching ReferenceId for a color in the Animal table, the AnimalName column will contain NULL for that particular row.

INNER JOIN

An INNER JOIN is a type of SQL join Query that combines rows from two or more tables based on a related column between them will have some common relation between the tables. The result includes only the rows where there is a match between the values in the specified columns.

We will run this query on same table of Animal and colors.

SELECT
    C.Name AS ColorName,
    A.Name AS AnimalName
FROM
    Color C
INNER JOIN
    Animal A ON A.ReferenceId = C.Id;

Output:

Explanation:

In this query:

• The INNER JOIN is used to join the Color table (C) with the Animal table (A) based on the condition that A.ReferenceId matches C.Id.
• The SELECT clause retrieves the color name (C.Name) and the corresponding animal name (A.Name) for each matching pair of Color and Animal.

This query will return only the rows where there is a match between the Color and Animal tables based on the specified condition.

Key Differences:

• INNER JOIN is primarily used for combining rows from different tables based on matching values in specified columns.
• CROSS APPLY is used to invoke a table-valued function for each row of the outer table, allowing more complex transformations and calculations.

In summary, INNER JOIN is a standard SQL operation for combining rows based on matching criteria, while CROSS APPLY is specific to SQL Server and is often used to apply table-valued functions for each row of the outer table. The choice between them depends on the specific requirements of your query and the data manipulation you need to perform.

Conclusion

It’s essential to consider the nature of your data and the logic you want to achieve when choosing between these operators. In Oracle databases (used with PL/SQL), the equivalent to CROSS APPLY is often achieved using OUTER APPLY or other constructs depending on the specific use case. Always refer to the documentation of the specific database you are using for details on available features and syntax.