Inner Join vs Cross Apply and Left Outer Join vs Outer Apply

Introduction

In SQL, joining tables is a fundamental operation for querying data across multiple tables. While traditional joins like INNER JOIN and LEFT OUTER JOIN are widely used, SQL Server introduces additional operators like CROSS APPLY and OUTER APPLY. Understanding the differences and appropriate use cases for these operators can significantly enhance the flexibility and power of your queries. This article explores the distinctions between INNER JOIN and CROSS APPLY, as well as LEFT OUTER JOIN and OUTER APPLY, providing insights into their functionalities and practical applications.

Inner Join vs. Cross Apply
 

Inner Join

An INNER JOIN combines rows from two tables based on a specified condition. It only includes rows where the condition is met.

Example. Let's consider two tables, Employees and Departments.

Employees Table

EmployeeID	Name	DepartmentID
1	Uday	1
2	Gopal	2
3	Bhavesh	3

Department Table

DepartmentID	DepartmentName
1	HR
2	IT
3	Finance

Query. An INNER JOIN to find employees and their respective department names.

SELECT e.Name, d.DepartmentName FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
Result:

Result

Name	DepartmentName
Uday	HR
Gopal	IT
Bhavesh	Finance

Cross Apply

CROSS APPLY is used to apply a table-valued function to each row of an outer table. It can join a table to a derived table or a table-valued function, dynamically producing a result set.

Example. Suppose we have a table-valued function that retrieves projects for each employee.

CREATE FUNCTION dbo.GetProjects(@EmployeeID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT ProjectName
    FROM Projects
    WHERE EmployeeID = @EmployeeID
)

Using CROSS APPLY to find employees and their projects.

Employees Table

EmployeeID	Name
1	Uday
2	Gopal

Projects Table

ProjectID	EmployeeID	ProjectName
1	1	Project A
2	1	Project B
3	2	Project C

Query

SELECT e.Name, p.ProjectName FROM Employees e
CROSS APPLY dbo.GetProjects(e.EmployeeID) p

Result

Name	ProjectName
Uday	Project A
Uday	Project B
Gopal	Project C

Left Outer Join vs. Outer Apply
 

Left Outer Join

A LEFT OUTER JOIN combines rows from two tables, including all rows from the left table even if there are no matching rows in the right table. Non-matching rows from the right table result in NULL values.

Example. Using the previous Employees and Departments tables, a LEFT OUTER JOIN to include all employees even if they do not belong to any department.

Query

SELECT e.Name, d.DepartmentName
FROM Employees e
LEFT OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID

Result

Name	DepartmentName
Uday	HR
Gopal	IT
Bhavesh	Finance

If the Employees table had an entry for Jayesh with a DepartmentName of NULL, the result would include the following:

Result

Name	DepartmentName
Uday	HR
Gopal	IT
Bhavesh	Finance
Jayesh	NULL

Outer Apply

OUTER APPLY functions similarly to LEFT OUTER JOIN but is used with table-valued functions. It includes all rows from the left table and applies the table-valued function to determine the matching rows from the right table.

Example. Using the Employees table and the dbo.GetProjects function, an OUTER APPLY to include all employees even if they do not have any projects:

Employees Table

EmployeeID	Name
1	Uday
2	Gopal
3	Jayesh

Projects Table

ProjectID	EmployeeID	ProjectName
1	1	Project A
2	1	Project B
3	2	Project C

Query

SELECT e.Name, p.ProjectName FROM Employees e
OUTER APPLY dbo.GetProjects(e.EmployeeID) p

Result

Name	ProjectName
Uday	Project A
Uday	Project B
Gopal	Project C
Jayesh	NULL

Here, OUTER APPLY includes all rows from the Employees table and applies the dbo.GetProjects function, resulting in NULL for employees without projects.

Why Use Apply?

1. Flexibility: CROSS APPLY and OUTER APPLY provide the ability to join with derived tables and table-valued functions, offering more flexibility than traditional joins.
2. Dynamic Queries: They allow for dynamic calculations and subqueries that depend on the outer query's row values.
3. Complex Scenarios: Useful in scenarios where the relationship between tables is not straightforward or static, enabling more complex data retrieval patterns.

Summary

While INNER JOIN and CROSS APPLY can sometimes produce similar results, they serve different purposes and are not equivalent. The same applies to LEFT OUTER JOIN and OUTER APPLY. The APPLY operators extend SQL’s capabilities by enabling dynamic, complex queries involving table-valued functions, providing greater flexibility and power than traditional joins. Understanding when and how to use these operators can significantly enhance your SQL querying skills.

If you find this article valuable, please consider liking it and sharing your thoughts in the comments.

Thank you, and happy coding.