OUTER APPLY in Sql Server

OUTER APPLY operator in Sql Server returns all rows from the LEFT table expression of the OUTER APPLY operator irrespective of whether it produces the corresponding result in the RIGHT table expression or not. The RIGHT table expression columns value will be NULL in the final result for those rows in the LEFT table expression that don’t produce the result from the RIGHT table expression. So from the result perspective we can say that the OUTER APPLY is similar to the classic LEFT OUTER JOIN

To understand OUTER APPLY in Sql Server with extensive list of examples, let us create the demo tables with sample data as shown in the following image by executing the following script:

apply-operator-demonstration-tables

CREATE DATABASE SqlHintsOUTERAPPLYDemo
GO
USE SqlHintsOUTERAPPLYDemo
GO
--Create Customers Table and Insert records
CREATE TABLE Customers 
( CustomerId INT, Name VARCHAR(50) )
GO
INSERT INTO Customers(CustomerId, Name) 
VALUES(1,'Shree'), (2,'Kalpana'), (3,'Basavaraj')
GO
--Create Orders Table and Insert records into it
CREATE TABLE Orders
(OrderId INT, CustomerId INT,Amount MONEY, OrderDate DATETIME)
GO
INSERT INTO Orders(OrderId, CustomerId,Amount,OrderDate) 
VALUES(100,1,100.0,Getdate()-30), (101,1,200.0,Getdate()-20),
      (103,1,300.0,Getdate()-10), (104,2,150.0,Getdate())	
GO

Create a Table Valued Function

Now execute the following script to create an inline Table valued function fnGetLastTwoCustomerOrders, which returns order details of the last 2 orders corresponding to the input customer id.

CREATE FUNCTION dbo.fnGetLastTwoCustomerOrders
( @CustomerId AS INT )  
RETURNS TABLE 
AS 
RETURN ( 
		SELECT TOP 2 * 
		FROM Orders O 
		WHERE O.CustomerId = @CustomerId 
		ORDER BY OrderDate DESC
	)

Let us verify this functions result for the customer id = 1

--Return last two order details for the customer id = 1
SELECT * FROM dbo.fnGetLastTwoCustomerOrders(1) O
GO

RESULT:
apply-operator-table-valued-function-result

OUTER APPLY Examples

Example 1: Demonstrates how we can re-write a very basic LEFT OUTER JOIN query by OUTER APPLY operator

OUTER APPLY is not an alternative for LEFT OUTER JOIN, instead each of them have their own pros and cons. The general rule of thumb is you may like to use the OUTER JOIN if you want to evaluate/execute the RIGHT side table expression or Table Valued function for each row in the LEFT side table expression. Just to demonstrates how we can use OUTER APPLY operator with a very basic example, here in this example will re-write a very basic LEFT OUTER JOIN query by OUTER APPLY.

We can write a query like below by using LEFT OUTER JOIN to get all Customers table rows irrespective of whether a corresponding record exists in the Orders table or not. For the records in the Customers table which don’t have matching records in the Orders table, the Orders table columns in the result will have NULL values

SELECT *
FROM Customers C
        LEFT JOIN Orders O
	   ON C.CustomerId = O.CustomerId

RESULT
left-outer-join-query

We can re-write the above INNER JOIN query using CROSS APPLY as shown below and still get the same result

SELECT *
FROM Customers C
  OUTER APPLY (SELECT * 
               FROM Orders O
     	       WHERE O.CustomerId = C.CustomerId) ORD

RESULT
outer-apply-left-outer-join-equivalent

From the above results we can see that the re-written OUTER APPLY query is returning the same result as that of the LEFT OUTER JOIN query.

[ALSO READ] CROSS APPLY in Sql Server

Example 2: OUTER APPLY operator between a Table and user defined Table Valued Function

We can write a query like below using OUTER APPLY operator to get details of all the customers with their last two order details if it is present. This query basically evaluates the function fnGetLastTwoCustomerOrders for each row in the Customer table by taking CustomerId as the input.

SELECT *
FROM dbo.Customers C
      OUTER APPLY 
        dbo.fnGetLastTwoCustomerOrders(C.CustomerId) O

RESULT:
sql-server-outer-apply-example-1

From the result we can see that for the customer with customer id =1, we have two orders in the result even though this customer has two orders because the function fnGetLastTwoCustomerOrders returns at max the last two orders for any given CustomerId. And for CustomerId =2, we see only one order detail in the result as this customer has placed only one order till now and for the Customer with CustomerId = 3 in the result we can see only the Customer details and all the Order details columns value for this customer is NULL because this customer is yet to place any order.

[ALSO]

[ALSO READ] Sql Server APPLY operator

Example 3: OUTER APPLY operator between a Table and Table Expression

In the example 2 the RIGHT side of the OUTER APPLY operator was User Defined Function, this example demonstrates how we can have a Table Expression on the right side of this operator.

The below script can also achieve the same result as the example 2, but here we have not used the Table Valued Function fnGetLastTwoCustomerOrders instead we have used the derived table.

SELECT *
FROM dbo.Customers C
	OUTER APPLY (SELECT TOP 2 * 
	  	     FROM Orders O 
		     WHERE O.CustomerId = C.CustomerId 
		     ORDER BY OrderDate DESC) ORD

RESULT:
sql-server-outer-apply-example-2

CROSS APPLY in Sql Server

CROSS APPLY operator invokes/executes a Table Valued User Defined Function for each row returned by the LEFT side table expression of the CROSS APPLY operator. It returns only those rows from the LEFT side table expression of the CROSS APPLY operator which produces the result from the RIGHT side Table Valued User Defined function/Table expression. So from the result perspective we can say that the CROSS APPLY is similar to that of the classic INNER JOIN.

To understand CROSS APPLY in Sql Server with extensive list of examples, let us create the demo tables with sample data as shown in the following image by executing the following script:

apply-operator-demonstration-tables

CREATE DATABASE SqlHintsCROSSAPPLYDemo
GO
USE SqlHintsCROSSAPPLYDemo
GO
--Create Customers Table and Insert records
CREATE TABLE Customers 
( CustomerId INT, Name VARCHAR(50) )
GO
INSERT INTO Customers(CustomerId, Name) 
VALUES(1,'Shree'), (2,'Kalpana'), (3,'Basavaraj')
GO
--Create Orders Table and Insert records into it
CREATE TABLE Orders
(OrderId INT, CustomerId INT,Amount MONEY, OrderDate DATETIME)
GO
INSERT INTO Orders(OrderId, CustomerId,Amount,OrderDate) 
VALUES(100,1,100.0,Getdate()-30), (101,1,200.0,Getdate()-20),
      (103,1,300.0,Getdate()-10), (104,2,150.0,Getdate())	
GO

Create a Table Valued Function

Now execute the following script to create an inline Table valued function fnGetLastTwoCustomerOrders, which returns order details of the last 2 orders corresponding to the input customer id.

CREATE FUNCTION dbo.fnGetLastTwoCustomerOrders
( @CustomerId AS INT )  
RETURNS TABLE 
AS 
RETURN ( 
		SELECT TOP 2 * 
		FROM Orders O 
		WHERE O.CustomerId = @CustomerId 
		ORDER BY OrderDate DESC
	)

Let us verify this functions result for the customer id = 1

--Return last two order details for the customer id = 1
SELECT * FROM dbo.fnGetLastTwoCustomerOrders(1) O
GO

RESULT:
apply-operator-table-valued-function-result

CROSS APPLY Examples

Example 1: Demonstrates how we can re-write a very basic INNER JOIN query by CROSS APPLY operator

CROSS APPLY is not an alternative for INNER JOIN, instead each of them have their own pros and cons. The general rule of thumb is you may like to use the CROSS JOIN if you want to evaluate/execute the RIGHT side table expression or Table Valued function for each row in the LEFT side table expression. Just to demonstrates how we can use CROSS APPLY operator with a very basic example, here in this example will re-write a very basic INNER JOIN query by CROSS APPLY.

We can write a query like below by using INNER JOIN to get the details of the Customers who have orders in the Orders table.

SELECT *
FROM Customers C
        INNER JOIN Orders O
	   ON C.CustomerId = O.CustomerId

RESULT
inner-join-query-result

We can re-write the above INNER JOIN query using CROSS APPLY as shown below and still get the same result

SELECT *
FROM Customers C
  CROSS APPLY (SELECT * 
               FROM Orders O
     	       WHERE O.CustomerId = C.CustomerId) ORD

RESULT
cross-apply-inner-join-equivalent

From the above results we can see that the re-written CROSS APPLY query is returning the same result as that of the INNER JOIN query.

[ALSO READ] OUTER APPLY in Sql Server

Example 2: CROSS APPLY operator between a Table and user defined Table Valued Function

We can write a query like below using CROSS APPLY operator to get details of all the customers with their last two order details. This query basically evaluates the function fnGetLastTwoCustomerOrders for each row in the Customer table by taking CustomerId as the input.

SELECT *
FROM dbo.Customers C
      CROSS APPLY 
        dbo.fnGetLastTwoCustomerOrders(C.CustomerId) O

RESULT:
sql-server-cross-apply-example-1

From the result we can see that for the customer with customer id =1, we have two orders in the result even though this customer has two orders because the function fnGetLastTwoCustomerOrders returns at max the last two orders for any given CustomerId. And for CustomerId =2, we see only one order detail in the result as this customer has placed only one order till now and the details of the Customer with CustomerId = 3 is not available in the result because the function fnGetLastTwoCustomerOrders doesn’t return any record as this this customer is yet to place any order.

[ALSO READ] Sql Server APPLY operator

Example 3: CROSS APPLY operator between a Table and Table Expression

In the example 2 the RIGHT side of the CROSS APPLY operator was User Defined Function, this example demonstrates how we can have a Table Expression on the right side of this operator.

The below script can also achieve the same result as that of example 2, but here we have not used the Table Valued Function fnGetLastTwoCustomerOrders instead we have used the derived table.

SELECT *
FROM dbo.Customers C
	CROSS APPLY (SELECT TOP 2 * 
	  	     FROM Orders O 
		     WHERE O.CustomerId = C.CustomerId 
		     ORDER BY OrderDate DESC) ORD

RESULT:
sql-server-cross-apply-example-2

SQL Server APPLY operator

APPLY operator was introduced in Sql Server 2005. The main purpose of the APPLY operator is to JOIN a Table with a Table Valued User Defined Function. The APPLY operator invokes/executes the Table Valued User Defined Function for each row returned by the LEFT side table expression of the APPLY operator.

APPLY operator can be used to join two table expressions just like any other JOIN operators. In case of APPLY operator, for each row in the LEFT side table expression of the APPLY operator the RIGHT side table expression is evaluated and final result can have the columns from both LEFT and RIGHT side table expressions just like in any other JOIN operations result.

Unlike APPLY operator, classic joins like INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN etc, doesn’t support evaluating a RIGHT side table valued function based on the column values of each row returned by the LEFT side table expression.

In other words the classic joins works on the self-sufficient sets of data, where these sets of data doesn’t depend on one another. Where-as in case of APPLY OPERATOR the RIGHT side data set is not self-sufficient, instead the APPLY operator uses values from the LEFT side table expression to define the RIGHT side data set.

Types of APPLY operators in Sql Server

There are two types of APPLY operators in Sql Server

CROSS APPLY

This operator returns only those rows from the LEFT side table expression of the CROSS APPLY operator which produces the result from the RIGHT side Table expression. So from the result perspective we can say that the CROSS APPLY is similar to that of the classic INNER JOIN operation

To understand CROSS APPLY with extensive list of examples, you may like to read CROSS APPLY in Sql Server

OUTER APPLY

This operator returns all the rows from the LEFT side table expression of the OUTER APPLY operator irrespective of whether it produces the corresponding result in the RIGHT side table expression or not. The RIGHT side table expression columns value will be NULL in the final result for those rows in the LEFT table expression that don’t produce the result from the RIGHT side table expression. So from the result perspective we can say that the OUTER APPLY is similar to the classic LEFT OUTER JOIN

To understand OUTER APPLY with extensive list of examples, you may like to read OUTER APPLY in Sql Server

Difference between LEFT OUTER JOIN and RIGHT OUTER JOIN in Sql Server

This article provides a comparative analysis of the Sql Server LEFT OUTER JOIN and RIGHT OUTER JOIN with extensive list of examples.

To understand the differences between Sql Server LEFT OUTER JOIN and RIGHT OUTER JOIN, let us create a demo database with two tables Customers and Orders with sample data as shown in the following image by executing the following script:

joins-demo-tables

CREATE DATABASE SqlHintsJoinDemo
GO
USE SqlHintsJoinDemo
GO
--Create Customers Table and Insert records
CREATE TABLE Customers 
( CustomerId INT, Name VARCHAR(50) )
GO
INSERT INTO Customers
VALUES(1,'Shree'), (2,'Kalpana'), (3,'Basavaraj')
GO
--Create Orders Table and Insert records into it
CREATE TABLE Orders (OrderId INT, CustomerId INT)
GO
INSERT INTO Orders 
VALUES(100,1), (200,4), (300,3)
GO

[ALSO READ] Joins In Sql Server

LEFT OUTER JOIN

RIGHT OUTER JOIN

DEFINITION

LEFT OUTER JOIN / LEFT JOIN returns all the rows from the LEFT table and the corresponding matching rows from the right table. If right table doesn’t have the matching record then for such records right table column will have NULL value in the result.

RIGHT OUTER JOIN / RIGHT JOIN returns all the rows from the RIGHT table and the corresponding matching rows from the left table. If left table doesn’t have the matching record then for such records left table column will have NULL value in the result.

VENN DIAGRAM
left-outer-join-venn-diagram

As per the above LEFT OUTER JOIN Venn Diagram, all the records from the left table (Customers) are returned regardless if any of those records have a match in the right table (Orders). It will also return any matching records from the right table (Orders)

right-outer-join-venn-diagram

As per the above RIGHT OUTER JOIN Venn Diagram, all the rows in the right table (Orders) are returned regardless if any of those records have a match in the left table (Customers). It will also return any matching records from the left table (Customers)

EXAMPLE

As per the data in our demo tables, Customers with CustomerId 1 and 3 in Customers table have the orders in the Orders table. Where as the customer with CustomerId 2 doesn’t have any order in the Orders table. So the LEFT JOIN on the CustomerId column between Customers and Orders table will return the Customer and Order details of the Customers with CustomerId 1 and 3 and for CustomerId 2 the Order Table columns will have NULL value in the result. Let us verify this by executing the following LEFT OUTER JOIN query:

SELECT *
FROM Customers C 
  LEFT OUTER JOIN Orders O
    ON O.CustomerId = C.CustomerId

RESULT:
left-outer-join-results

As per the data in our demo tables, only for the order with OrderId 200 we don’t have it’s corresponding customer info with CustomerId 4 in the Customers table. And for the other two orders, the corresponding customer info is present in the Customers Table. So for the orders with CustomerId 1 and 3 will have customer details and for the order with CustomerId 4, the Customers table columns will have NULL value in the result. Let us verify this by executing the following RIGHT OUTER JOIN query:

SELECT *
FROM Customers C 
  RIGHT OUTER JOIN Orders O
    ON O.CustomerId = C.CustomerId

RESULT
right-outer-join-result

EXECUTION PLAN
Let us execute the following query and verify it’s execution plan:

SELECT *
FROM Customers C 
  LEFT OUTER JOIN Orders O
    ON O.CustomerId = C.CustomerId

left-join-execution-plan

From the execution plan we can see that first all records from the LEFT TABLE (i.e. Customers) of the JOIN clause are fetched. Then for each of these LEFT TABLE (i.e. Customers) records, it is trying to fetch the matching data from the RIGHT TABLE (i.e. Orders TABLE) using Nested Loops LEFT OUTER JOIN operation.

Let us execute the following query and verify it’s execution plan:

SELECT *
FROM Customers C 
  RIGHT OUTER JOIN Orders O
    ON O.CustomerId = C.CustomerId

RESULT:
right-join-execution-plan

From the execution plan we can see that first all records from the RIGHT TABLE (i.e. Orders) of the JOIN clause are fetched. Then for each of these LEFT TABLE (i.e. Orders) records, it is trying to fetch the matching data from the RIGHT TABLE (i.e. Customers TABLE) using Nested Loops LEFT OUTER JOIN operation. From the execution plan we can observe that Sql Server indirectly converted this RIGHT OUTER JOIN query to a LEFT OUTER JOIN query like below before execution:

SELECT *
FROM Orders O 
  LEFT OUTER JOIN Customers O
    ON O.CustomerId = C.CustomerId 

So, to get the result from LEFT OUTER JOIN which is same as that of RIGHT OUTER JOIN and vice-versa, we just need to switch the order in which the tables appear in the JOIN clause.

ALTERNATIVE DEFINITION
LEFT JOIN = INNER JOIN + Rows from LEFT Table which doesn’t have matching record in the RIGHT table RIGHT JOIN = INNER JOIN + Rows from RIGHT Table which doesn’t have matching record in the LEFT table

CONCLUSION

The main difference between LEFT OUTER JOIN and RIGHT OUTER JOIN is: LEFT OUTER JOIN returns all records from the LEFT table irrespective of whether there are any matching rows in the RIGHT table. Where as RIGHT OUTER JOIN returns all records from the RIGHT table irrespective of whether there are any matching rows in the LEFT table. To get the result from LEFT OUTER JOIN which is same as that of RIGHT OUTER JOIN and vice-versa, we just need to switch the order in which the tables appear in the JOIN clause.

ALSO READ

Difference between LEFT JOIN and LEFT OUTER JOIN in Sql Server

Frankly speaking, in Sql Server there is no difference between LEFT JOIN and LEFT OUTER JOIN. They produce the same result and also the same performance.

Let us prove with examples that there is no difference between LEFT JOIN and LEFT OUTER JOIN. Execute the following script to create a demo database with two tables Customers and Orders with sample data as shown in the following image:

inner-join-demo-tables

CREATE DATABASE SqlHintsJoinDemo
GO
USE SqlHintsJoinDemo
GO
--Create Customers Table and Insert records
CREATE TABLE Customers 
( CustomerId INT, Name VARCHAR(50) )
GO
INSERT INTO Customers
VALUES(1,'Shree'), (2,'Kalpana'), (3,'Basavaraj')
GO
--Create Orders Table and Insert records into it
CREATE TABLE Orders (OrderId INT, CustomerId INT)
GO
INSERT INTO Orders 
VALUES(100,1), (200,4), (300,3)
GO

[ALSO READ] Joins In Sql Server

LEFT JOIN and LEFT OUTER JOIN produces the same result

Below table demonstrates that both LEFT JOIN and LEFT OUTER JOIN produces the same result.

LEFT JOIN

LEFT OUTER JOIN

SELECT * 
FROM Customers C 
  LEFT JOIN Orders O
    ON O.CustomerId = C.CustomerId

RESULT:
left-join-results

SELECT * 
FROM Customers C 
  LEFT OUTER JOIN Orders O
    ON O.CustomerId = C.CustomerId

RESULT:
left-outer-join-results

LEFT JOIN and LEFT OUTER JOIN Execution Plan Comparison

From the below execution plans comparison, we can see that both LEFT JOIN and LEFT OUTER JOIN are producing the same plan and performance. Here I have used the Sql Server 2016 “Compare Execution Plans” feature to compare the execution plan produced by LEFT JOIN and LEFT OUTER JOIN. This feature provides an option to highlight the similar operations and we can see that both LEFT JOIN and LEFT OUTER JOIN is producing the similar operations and they are highlighted in the same color.


left-join-and-left-outer-join-execution-plan-comparision

LEFT JOIN and LEFT OUTER JOIN produces the same performance counters

The below performance counters (i.e. execution plan properties) comparison shows that both LEFT JOIN and LEFT OUTER JOIN produces the same performance result


left-join-and-left-outer-join-execution-plan-properties-comparision

CONCLUSION

From the above various examples we can see that there is absolutely NO DIFFERENCE between LEFT JOIN and LEFT OUTER JOIN. Even though both produces the same result and performance, I would prefer using LEFT OUTER JOIN instead of just LEFT JOIN. As it is more readable and leaves no ambiguity. Please share which one you use and reason for the same.

ALSO READ