For database developers and SQL enthusiasts, the realm of T-SQL joins is as crucial as the backbone is to the body. Amidst the familiar names like INNER JOIN and LEFT JOIN, two less explored yet exceedingly powerful joins — OUTER APPLY and CROSS APPLY — await their spotlight. In this in-depth exploration, we'll dissect these critical tools in your SQL armory and demonstrate how they can transform your queries, unlocking new pathways for data manipulation and performance optimization.
An Overview of T-SQL Joins and the Need for Outer Apply and Cross Apply in SQL Server
INNER JOIN
An INNER JOIN operation fetches rows from both tables that meet the join condition. If a row in the first table matches one or more rows in the second table according to the specified join predicate, those rows will be combined to form a result row. Rows in product table that do not find a match in the other table are excluded from the results, making INNER JOIN a selective join that only retrieves matching pairs.
Example Of Inner Join:
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;This query retrieves order IDs along with the names of customers who placed those orders. Only orders with a corresponding customer record are included sample data, in above sample query below.
LEFT JOIN (or LEFT OUTER JOIN)
A LEFT JOIN (also known as a LEFT OUTER JOIN) returns all rows from the left table, along with matched rows from the right table. If there is no match between two or more tables, the result of cross join set will include NULL values for the columns from the right table. This type of join ensures that every row from the left table appears in the left outer join result, regardless of whether a matching row exists in the right table.
Example Of Left Join
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;This query lists all employees, including those not assigned to any department. Employees without a department will have no NULL value in the DepartmentName field.
RIGHT JOIN (or RIGHT OUTER JOIN)
Conversely, a RIGHT JOIN (or RIGHT OUTER JOIN) returns all rows from the right table and the matched rows from the left table. For rows in the right table that do not have corresponding matches in the left table, the result set will include NULL values for all the rows and columns from the left table. This join ensures that every row from the right table is represented in the result.
Example Right Join:
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;This query would list the same results for all customers, including only those rows who have never placed an order. Orders without a corresponding customer will have NULL values in the result.
FULL JOIN (or FULL OUTER JOIN)
A FULL JOIN combines the effects of both LEFT JOIN and RIGHT JOIN. It returns rows when there is a match in either the left table, the right table, or both. Essentially, it produces a complete set of records from both tables, with NULLs in places where a match does not exist on one side of the join.
Example Full Join:
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
FULL JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;This query retrieves all employees and all departments, including employees without departments and departments without employees.
Each of these JOIN types serves a unique purpose depending on the data retrieval needs of return data in your query, allowing for flexible and powerful data analysis and reporting capabilities.
The Need for APPLY: CROSS APPLY and OUTER APPLY
While traditional joins are powerful, they have limitations, especially when attempting to join rows derived tables together with a table-valued function (TVF) or when dealing with complex expressions that must be evaluated row by table variable, by row. This is where CROSS APPLY and OUTER APPLY join operations may come into play.
CROSS APPLY:
Works similarly to an INNER JOIN but is designed to allow joining a left table with a TVF or complex expression that requires input from the left table. It returns rows from the primary table only if there is a corresponding output from the TVF or expression. This makes it ideal for scenarios where you need to filter the main query to records that match the result set generated by the TVF or expression (SQL Shack, Thwack).
OUTER APPLY:
Functions similarly to a LEFT JOIN in that it returns all rows from the left table (primary query set) even if the right table expression (e.g., a TVF) returns no matching rows. For those left table rows without corresponding right table rows, OUTER APPLY fills the columns with NULL values. This operation is particularly useful when no easy join exists and when ensuring that every row from the primary dataset is represented in the output is crucial above query. (MSSQLTips, Navicat).
Definition and Usage - Outer Apply in T-SQL
OUTER APPLY is an operator in T-SQL used to invoke a table-valued function for each row returned by the outer table expression. It's similar to CROSS APPLY, but it returns all rows from the left table expression, even if the right table expression returns no rows.
SQL Server Syntax:
SELECT columns
FROM OuterTable
OUTER APPLY TableValuedFunction(OuterTable.Column) AS AliasHere's a simple example:
Suppose you have two tables: Employees and Salaries. You want to retrieve all employees and their salaries, if available. You have a table-valued function GetSalaries that takes an employee ID and returns their salaries.
Table Definition:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName NVARCHAR(100)
);
CREATE TABLE Salaries (
EmployeeID INT,
Salary DECIMAL(10, 2)
);
INSERT INTO Employees VALUES (1, 'John'), (2, 'Alice');
INSERT INTO Salaries VALUES (1, 50000), (3, 60000);Table-Valued Function:
CREATE FUNCTION GetSalaries (@EmployeeID INT)
RETURNS TABLE
AS
RETURN (
SELECT Salary
FROM Salaries
WHERE EmployeeID = @EmployeeID
);Query using OUTER APPLY:
SELECT E.EmployeeID, E.EmployeeName, S.Salary
FROM Employees E
OUTER APPLY GetSalaries(E.EmployeeID) AS S;The Outer Apply Operator Returns
EmployeeID | EmployeeName | Salary
-----------|--------------|---------
1 | John | 50000.00
2 | Alice | NULLIn the output:
John's salary is retrieved from the Salaries table.
Alice's salary is not available in the Salaries table, so it appears as NULL.
Definition and Purpose - Cross Apply in T-SQL
CROSS APPLY is another operator in T-SQL used to invoke a left table expression re-valued function for each row returned by the outer table expression. Unlike OUTER APPLY, it returns only the rows for which the right table expression produces a result.
SQL Server Syntax:
SELECT columns
FROM OuterTable
CROSS APPLY TableValuedFunction(OuterTable.Column) AS AliasHere's an example:
Suppose you have two tables: Departments and Employees. You want to retrieve all departments along with the employees working in each department. You have a table-valued function call: GetEmployeesByDepartment that takes a department ID and returns the employees in that department.
Table Definition:
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName NVARCHAR(100)
);
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName NVARCHAR(100),
DepartmentID INT
);
INSERT INTO Departments VALUES (1, 'IT'), (2, 'HR');
INSERT INTO Employees VALUES (1, 'John', 1), (2, 'Alice', 2), (3, 'Bob', 1);
Table-Valued Function:
CREATE FUNCTION GetEmployeesByDepartment (@DepartmentID INT)
RETURNS TABLE
AS
RETURN (
SELECT EmployeeName
FROM Employees
WHERE DepartmentID = @DepartmentID
);Query using CROSS APPLY:
SELECT D.DepartmentID, D.DepartmentName, E.EmployeeName
FROM Departments D
CROSS APPLY GetEmployeesByDepartment(D.DepartmentID) AS E;Cross Apply Operator Returns
DepartmentID | DepartmentName | EmployeeName
-------------|----------------|--------------
1 | IT | John
1 | IT | Bob
2 | HR | AliceIn the output:
For the IT, department table, both John and Bob are retrieved from the Employees table.
For the HR department, only Alice is retrieved, as there's no other employee in that department.
When To Use Cross Apply and Outer apply In SQL
The choice between CROSS APPLY and OUTER APPLY in T-SQL depends on the desired behavior and the data you're working with. Here are some guidelines:
Use The CROSS APPLY Operator when:
You want to filter the rows from the outer table based on the results of the table-valued function.
You want to select statement exclude rows from the outer table for which the table-valued function returns no rows.
You want to perform an inner join-like operation between the outer table and the table-valued function.
Example: Suppose you have a table of orders and a table-valued function that returns the products ordered for each order. You want to retrieve all orders along with the products ordered in each order. If an order has no products, you're not interested in including it in the result set.
Use OUTER APPLY Operator when:
You want to include all rows from the outer table in the result set, regardless of whether the table-valued function returns any rows.
You want to perform a left join-like operation between the outer table and the table-valued function.
You want to preserve rows from the outer table even if there are no corresponding rows in the table-valued function.
Example: Suppose you have a table of employees and a table-valued function that returns the tasks assigned to each employee. You want to retrieve all employees along with the tasks assigned to them. Even if an employee has no tasks assigned, you still want to include that employee in the result set.
In summary, choose CROSS APPLY when you want to filter or exclude rows from two table columns the outer table based on the results of the table-valued function, and choose OUTER APPLY when you want to include all rows from two table expressions the same cross apply and outer table regardless of the results of the table-valued function.
Benefits of Using Outer Apply and Cross Apply
Improved Query Performance
Outer Apply and Cross Apply can significantly reduce the query execution time and memory consumption by minimizing the need for multiple subqueries and joins. By eliminating the need to run separate queries query joins, the use of these joins can streamline complex data operations and enhance database performance.
Handling Complex Data Relationships
In scenarios where data relationships are intricate and non-linear, such as hierarchical queries or those requiring significant data transformation, OUTER and CROSS APPLY can simplify the SQL, making it more readable and maintainable. Their ability to handle complex data structures effectively can lead to more agile development and quicker troubleshooting.
Practical Applications of CROSS APPLY:
Splitting Strings into Rows:
Suppose you have a column with comma-separated values, and you want to split these values into separate rows. You can use STRING_SPLIT function (available in SQL Server 2016 and later) with CROSS APPLY.
-- Create a sample table
CREATE TABLE SomeTable (
ID INT PRIMARY KEY,
Column NVARCHAR(100)
);
-- Insert sample data
INSERT INTO SomeTable (ID, Column)
VALUES
(1, 'apple,banana,orange'),
(2, 'grape,kiwi'),
(3, 'watermelon');
-- Query using CROSS APPLY and STRING_SPLIT
SELECT s.value
FROM SomeTable t
CROSS APPLY STRING_SPLIT(t.Column, ',') s;
Calculating Aggregates per Group:
If you need to calculate aggregates for each group in a table, you can use CROSS APPLY with aggregate functions.
-- Create Groups table
CREATE TABLE Groups (
GroupID INT PRIMARY KEY,
GroupName NVARCHAR(100)
);
-- Insert sample data into Groups table
INSERT INTO Groups (GroupID, GroupName)
VALUES
(1, 'Group A'),
(2, 'Group B'),
(3, 'Group C');
-- Create Data table
CREATE TABLE Data (
DataID INT PRIMARY KEY,
GroupID INT,
Value INT
);
-- Insert sample data into Data table
INSERT INTO Data (DataID, GroupID, Value)
VALUES
(1, 1, 10),
(2, 1, 15),
(3, 1, 20),
(4, 2, 25),
(5, 2, 30),
(6, 3, 35),
(7, 3, 40),
(8, 3, 45);
SELECT g.GroupID, AVG(t.Value) AS AvgValue
FROM Groups g
CROSS APPLY (
SELECT Value
FROM Data d
WHERE d.GroupID = g.GroupID
) t
GROUP BY g.GroupID;Unpivot Data:
CROSS APPLY can be used to unpivot data from wide tables to long tables.
-- Create YourTable
CREATE TABLE YourTable (
ID INT PRIMARY KEY,
Attribute1 NVARCHAR(100),
Attribute2 NVARCHAR(100),
Attribute3 NVARCHAR(100)
);
-- Insert sample data into YourTable
INSERT INTO YourTable (ID, Attribute1, Attribute2, Attribute3)
VALUES
(1, 'Value1-1', 'Value1-2', 'Value1-3'),
(2, 'Value2-1', 'Value2-2', 'Value2-3'),
(3, 'Value3-1', 'Value3-2', 'Value3-3');
SELECT u.ID, u.Attribute, u.Value
FROM YourTable t
CROSS APPLY (
VALUES ('Attribute1', t.Attribute1),
('Attribute2', t.Attribute2),
('Attribute3', t.Attribute3)
) u(Attribute, Value);
Practical Applications of OUTER APPLY:
Getting Related Data:
When you want to retrieve related data from another table and ensure that all rows from the primary table are returned, you can use OUTER APPLY.
-- Create YourTable
CREATE TABLE YourTable (
ID INT PRIMARY KEY,
Attribute1 NVARCHAR(100),
Attribute2 NVARCHAR(100),
Attribute3 NVARCHAR(100)
);
-- Insert sample data into YourTable
INSERT INTO YourTable (ID, Attribute1, Attribute2, Attribute3)
VALUES
(1, 'Value1-1', 'Value1-2', 'Value1-3'),
(2, 'Value2-1', 'Value2-2', 'Value2-3'),
(3, 'Value3-1', 'Value3-2', 'Value3-3');
SELECT o.OrderID, od.ProductID, p.ProductName
FROM Orders o
OUTER APPLY (
SELECT TOP 1 *
FROM OrderDetails od
WHERE od.OrderID = o.OrderID
) od
LEFT JOIN Products p ON od.ProductID = p.ProductID;
Handling Optional Related Data:
When you have optional related data and want to include it in the result set along with all rows from the primary table, OUTER APPLY can be useful.
-- Create Employees table
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName NVARCHAR(100),
DepartmentID INT
);
-- Insert sample data into Employees table
INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID)
VALUES
(1, 'John', 1),
(2, 'Alice', 2),
(3, 'Bob', 1),
(4, 'Emma', NULL); -- Employee without a department
-- Create Departments table
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName NVARCHAR(100)
);
-- Insert sample data into Departments table
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES
(1, 'IT'),
(2, 'HR'),
(3, 'Finance');
SELECT e.EmployeeID, e.EmployeeName, d.DepartmentName
FROM Employees e
OUTER APPLY (
SELECT TOP 1 DepartmentName
FROM Departments d
WHERE d.DepartmentID = e.DepartmentID
) d;
When To Use Outer Apply Vs Table Valued Functions
Understanding when to use OUTER APPLY versus table-valued functions (TVFs) in T-SQL is essential for efficiently querying and managing data in SQL Server. While both mechanisms serve the purpose of combining data from different sources, their applications and implications on query performance can differ significantly.
OUTER APPLY
The OUTER APPLY operation allows you to join a left table (the primary query set) with a right table expression that can be a table-valued function, following example, which might accept parameters from the left table. A key characteristic of OUTER APPLY is its ability to return all rows from the left of table expressions even if the right table expression (e.g., a table-valued function) returns no matching rows for those left table rows. In such cases, the result of null primary key set will include NULL values for columns coming from the right table expression.
This functionality is particularly useful when you need to ensure that every row from the primary dataset is represented in the output, regardless of whether there is a corresponding result from the table-valued function or not. It's akin to performing a LEFT JOIN but with the added capability to work dynamically with row-specific calculations or transformations provided by a table-valued function.
Use Case Example:
SELECT Users.*, UserDetails.*
FROM Users
OUTER APPLY (SELECT * FROM GetUserDetails(Users.UserID) AS UserDetails)
-- GetUserDetails is a table-valued function that returns user details for a given UserID.
In this example, OUTER APPLY ensures that even if GetUserDetails does not return any rows for some Users.UserID, only those rows of users will still be included in the final result set with NULL values for the UserDetails columns.
Table-Valued Functions (TVFs)
Table-valued functions, on the other hand, are functions that return a table data type. These can be used within a query much like a regular table and are particularly powerful for encapsulating complex logic or operations that need to be reused across multiple queries. However, when joining table valued functions is used directly in a query, the function is executed independently of any outer query and above function does not have the ability to dynamically accept row-by-row inputs from an outer table unless explicitly joined using mechanisms like CROSS APPLY or OUTER APPLY.
Use Case Example:
SELECT *
FROM Users
JOIN dbo.GetUserDetails(Users.UserID) ON 1=1
-- This will not work as intended because TVFs cannot be directly joined like this without APPLY.To correctly utilize a TVF that requires parameters from another table, you need to employ APPLY operators.
Sources:
Conclusion
The document provides a comprehensive overview of the usage and benefits of `OUTER APPLY` and `CROSS APPLY` in T-SQL. It contrasts their functionalities with traditional joins and demonstrates their effectiveness in handling complex data relationships and improving query performance. The explanation clarifies when to use each type, with `CROSS APPLY` benefiting scenarios where filtering of the outer table's rows is needed, and `OUTER APPLY` being advantageous for including all rows from the outer table, regardless of the table-valued function's results. Furthermore, it elaborates on practical applications, giving examples where each apply operator can be particularly useful, such as splitting strings into rows, performing group-wise aggregates, unpivoting data, retrieving related data, handling optional related data, and conducting conditional joins. In essence, this document serves as a valuable resource for database developers and SQL practitioners, highlighting the strategic use of apply operators to streamline complex queries and enhance database operation efficiency.