Using OUTER APPLY in SQL Server

Hello everyone,

In this article, I will share information about using OUTER APPLY in SQL Server.

In SQL Server, OUTER APPLY is a JOIN operator that allows you to apply data from one table or query to another table or query. OUTER APPLY, like CROSS APPLY, is one of the APPLY operators in SQL Server’s T-SQL language.

In SQL Server, OUTER APPLY is a type of JOIN and is typically used with a subquery or a table-valued function (TVF). For each row of the query, OUTER APPLY applies the subquery or TVF to its right and returns matching results. If there is no match, it returns NULL values, as in LEFT OUTER JOIN.

The use of OUTER APPLY in SQL Server is especially useful when a subquery or TVF (Table Valued Function) needs to be applied for each row of a table. This is especially useful with the use of user-created functions or sub-queries in complex queries.

To understand OUTER APPLY in SQL Server, let’s create the following table and examine the query.

CREATE TABLE Calisanlar (
	CalisanID INT PRIMARY KEY
   ,Adi VARCHAR(50) NOT NULL
   ,Soyadi VARCHAR(50) NOT NULL
   ,DepartmanID INT NOT NULL
   ,Maas DECIMAL(10, 2) NOT NULL
);

CREATE TABLE Departmanlar (
	DepartmanID INT PRIMARY KEY
   ,DepartmanAdi VARCHAR(50) NOT NULL
);

INSERT INTO Calisanlar (CalisanID, Adi, Soyadi, DepartmanID, Maas)
	VALUES (1, 'Ahmet', 'Yilmaz', 1, 5000.00);

INSERT INTO Calisanlar (CalisanID, Adi, Soyadi, DepartmanID, Maas)
	VALUES (2, 'Mehmet', 'Kaya', 1, 6000.00);

INSERT INTO Calisanlar (CalisanID, Adi, Soyadi, DepartmanID, Maas)
	VALUES (3, 'Ayse', 'Ozturk', 2, 4500.00);

INSERT INTO Calisanlar (CalisanID, Adi, Soyadi, DepartmanID, Maas)
	VALUES (4, 'Fatma', 'Aydin', 2, 5500.00);

INSERT INTO Departmanlar (DepartmanID, DepartmanAdi)
	VALUES (1, 'Muhasebe');

INSERT INTO Departmanlar (DepartmanID, DepartmanAdi)
	VALUES (2, 'Insan Kaynaklari');

SELECT
	D.DepartmanAdi
   ,DA.OrtalamaMaas
FROM Departmanlar D
OUTER APPLY (SELECT
		AVG(Maas) AS OrtalamaMaas
	FROM Calisanlar C
	WHERE C.DepartmanID = D.DepartmanID) DA

The query above returns the name and average salary of each department. OUTER APPLY joins each department with the “Departments” table and runs a subquery for each department. This subquery calculates the average value of the salaries of other employees working in the same department. If there are no employees in a department, AverageMaas is returned as NULL.

The above query may sound a bit complicated. We can overcome this by using a function that returns a table. First, let’s create the following function.

CREATE FUNCTION GetOrtalamaMaas (@DepartmanID INT)
RETURNS DECIMAL(10, 2)
AS
BEGIN
	DECLARE @OrtalamaMaas DECIMAL(10, 2)
	SELECT
		@OrtalamaMaas = AVG(Maas)
	FROM Calisanlar
	WHERE DepartmanID = @DepartmanID
	RETURN @OrtalamaMaas
END

Now let’s use this function together with the OUTER APPLY statement.

SELECT
	D.DepartmanAdi
   ,dbo.GetOrtalamaMaas(D.DepartmanID) AS OrtalamaMaas
FROM Departmanlar D

The query above returns the name and average salary of each department. We can get a similar result by using function instead of OUTER APPLY. The function provides many benefits to the user in SQL Server, especially in complex queries, and can be used as a reusable component over and over again.

In SQL Server, OUTER APPLY can be used especially when working with subquery or table-valued functions. By combining the results of the function or subquery with the main query result, it can present the results in a more meaningful and more readable way.

Good luck to everyone in business and life.