When Should We Use CROSS APPLY Over INNER JOIN?

In SQL Server, both INNER JOIN and CROSS APPLY are used to combine data from multiple tables, but they serve different purposes and have distinct use cases. INNER JOIN is typically used to match rows between two tables based on a related column, returning only the rows where a match exists in both tables.

On the other hand, CROSS APPLY allows for more flexibility, especially in scenarios that require row-wise operations or dynamic subqueries for each row. In this article, When should we use CROSS APPLY over INNER JOIN in detail by understanding various examples and so on.

Understanding INNER JOIN

INNER JOIN in SQL Server 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 found in both tables.

The syntax for INNER JOIN in SQL Server is as follows:

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Understanding CROSS APPLY

CROSS APPLY is a specific feature in SQL Server that is especially useful when dealing with table-valued functions or correlated subqueries. It allows row-wise operations and can apply subqueries dynamically for each row from the outer table.

The syntax for CROSS APPLY in SQL Server is as follows:

SELECT column_list
FROM outer_table
CROSS APPLY (
    -- Subquery or table-valued function
    inner_table_expression
) AS alias;

Setting Up Environment

For fully understanding CROSS APPLY and INNER JOIN in SQL Server, we will create 2 tables, write queries, and understand the output one by one.

-- Creating the 'employees' table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT
);

-- Creating the 'departments' table
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

-- Inserting data into the 'employees' table
INSERT INTO employees (employee_id, name, department_id)
VALUES (1, 'John Doe', 1),
       (2, 'Alice Johnson', 2),
       (3, 'Michael Brown', 1);

-- Inserting data into the 'departments' table
INSERT INTO departments (department_id, department_name)
VALUES (1, 'HR'),
       (2, 'Engineering');

Example of When to Use CROSS APPLY Over INNER JOIN in SQL Server

Example 1: INNER JOIN to Retrieve Employees with Their Department Names

In this example, we will combine data from the employees and departments tables using INNER JOIN.

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

Output:

Explanation: In this example the query uses an INNER JOIN to combine data from the employees and departments tables based on their department_id. It retrieves the names of employees along with the names of the departments they belong to.

Example 2: INNER JOIN with Conditions

In this example, we use INNER JOIN with a filter to get the names of employees related to a particular department.

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id
WHERE departments.department_name = 'HR';

Output:

Explanation: In this we apply an INNER JOIN between employees and departments, but with a condition to filter the results. This query retrieves the names of employees who belong to the 'HR' department.

Example 3: CROSS APPLY to Retrieve Employees with Department Info

In this example, we will use CROSS APPLY to get the employees with their department details.

SELECT employees.name, department_info.department_name
FROM employees
CROSS APPLY (
    SELECT department_name
    FROM departments
    WHERE employees.department_id = departments.department_id
) AS department_info;

Output:

Explanation: In this the query uses a CROSS APPLY operation, allowing us to correlate the employees and departments tables row by row. It retrieves all employees along with their corresponding department names.

Example 4: CROSS APPLY with Conditions

In this example, we will filter the results obtained from CROSS APPLY based on conditions.

SELECT employees.name, department_info.department_name
FROM employees
CROSS APPLY (
    SELECT department_name
    FROM departments
    WHERE employees.department_id = departments.department_id
      AND departments.department_name = 'HR'
) AS department_info;

Output:

Explanation: Here, we use CROSS APPLY with conditions to filter the results. This query retrieves the names of employees who belong to the 'HR' department only.

Conclusion

While both INNER JOIN and CROSS APPLY are powerful tools in SQL Server, they are suited for different types of operations. INNER JOIN is ideal for combining datasets based on common keys and returning matching rows. However, when dealing with dynamic subqueries or operations that need to be applied on a row-by-row basis, CROSS APPLY offers a more flexible and efficient solution.