How to use CROSS APPLY and OUTER APPLY correctly in SQL Server
In this article we are going to learn how to join two tables using CROSS APPLY and OUTER APPLY in SQL Server with examples. SQL Server introduced the APPLY operator in 2005.
CROSS APPLY is equivalent to INNER JOIN.
OUTER APPLY is equivalent to LEFT JOIN.
Refer below tables for the following examples.
Denormalized Data
Table: EmployeeData table
Id | Code | DepartmentId | DepartmentName |
1 | 0001 | 1 | Producing |
2 | 0002 | 2 | HR |
3 | 0003 | 1 | Producing |
4 | 0004 | NULL | NULL |
5 | 0005 | 3 | Marketing |
6 | 0006 | NULL | NULL |
Table: Employees
Id | Code | DepartmentId |
1 | 0001 | 1 |
2 | 0002 | 2 |
3 | 0003 | 1 |
4 | 0004 | NULL |
5 | 0005 | 3 |
6 | 0006 | NULL |
Sample data for employees
Table: Departments
Id | Name |
1 | Producing |
2 | HR |
3 | Marketing |
4 | IT |
5 | Accounting |
Sample data for departments
Use SQL Server or MySQL to run the following query to create sample tables for given examples.
CREATE TABLE Employees( Id int NOT NULL, Code varchar(10) NOT NULL, DepartmentId int NULL, PRIMARY KEY (Id) );
CREATE TABLE Departments( Id int NOT NULL, Name varchar(10) NOT NULL, PRIMARY KEY (Id) );
INSERT Employees VALUES (1, '0001', 1); INSERT Employees VALUES (2, '0002', 2); INSERT Employees VALUES (3, '0003', 1); INSERT Employees VALUES (4, '0004', NULL); INSERT Employees VALUES (5, '0005', 3); INSERT Employees VALUES (6, '0001', NULL);
INSERT Departments VALUES (1, 'Production'); INSERT Departments VALUES (2, 'HR'); INSERT Departments VALUES (3, 'Marketing'); INSERT Departments VALUES (4, 'IT'); INSERT Departments VALUES (5, 'Accounting'); |
Query to create tables with sample data
CROSS APPLY / INNER JOIN
INNER JOIN / CROSS APPLY selects all matching rows from both tables. CROSS APPLY is equivalent to INNER JOIN.
INNER JOIN/CROSS APPLY
Example #1: INNER JOIN
The following SQL statement shows how to create an INNER JOIN using two tables.
SQL Statement / Example(s) |
SELECT Employees.Id, Employees.Code, Departments.Name AS Department FROM Employees INNER JOIN Departments ON Employees.DepartmentId = Departments.Id |
INNER JOIN in SQL
Example #2: CROSS APPLY
The following SQL statement shows how to create a CROSS APPLY using two tables.
SQL Statement / Example(s) |
SELECT E.Id, E.Code, A.Name FROM Employees E CROSS APPLY ( SELECT * FROM Departments D WHERE E.DepartmentId = D.Id ) A |
CROSS APPLY in SQL
Id | Code | Department |
1 | 0001 | Production |
2 | 0002 | HR |
3 | 0003 | Production |
5 | 0005 | Marketing |
Query Result
LEFT JOIN / OUTER APPLY
LEFT JOIN / OUTER APPLY selects all matching rows from the left table. OUTER APPLY is equivalent to LEFT JOIN.
LEFT JOIN / LEFT APPLY
Example #3: LEFT JOIN
The following SQL statement shows how to create an LEFT JOIN using two tables.
SQL Statement / Example(s) |
SELECT Employees.Id, Employees.Code, Departments.Name AS Department FROM Employees LEFT JOIN Departments ON Employees.DepartmentId = Departments.Id |
LEFT JOIN in SQL
Example #4: OUTER APPLY
The following SQL statement shows how to create an OUTER APPLY using two tables.
SQL Statement / Example(s) |
SELECT E.Id, E.Code, A.Name FROM Employees E OUTER APPLY ( SELECT * FROM Departments D WHERE E.DepartmentId = D.Id ) A GO |
OUTER APPLY in SQL
Id | Code | Department |
1 | 0001 | Production |
2 | 0002 | HR |
3 | 0003 | Production |
4 | 0004 | NULL |
5 | 0005 | Marketing |
6 | 0006 | NULL |
Query Result
