Tag Archives: SQL SERVER RIGHT OUTER JOIN vs RIGHT JOIN

Difference between RIGHT JOIN and RIGHT OUTER JOIN in Sql Server

Differences, Sql Server, , , , , , ,

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

Let us prove with examples that there is no difference between RIGHT JOIN and RIGHT 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:

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] Difference between LEFT OUTER JOIN and RIGHT OUTER JOIN

RIGHT JOIN and RIGHT OUTER JOIN produces the same result

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

RIGHT JOIN

RIGHT OUTER JOIN

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

RESULT:
right-join-result 

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

RESULT:
right-outer-join-result

RIGHT JOIN and RIGHT OUTER JOIN Execution Plan Comparison

From the below execution plans comparison, we can see that both RIGHT JOIN and RIGHT 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 RIGHT JOIN and RIGHT OUTER JOIN. This feature provides an option to highlight the similar operations and we can see that both RIGHT JOIN and RIGHT OUTER JOIN is producing the similar operations and they are highlighted in the same color.


right-join-vs-right-outer-join-execution-plan-comparision

RIGHT JOIN and RIGHT OUTER JOIN produces the same performance counters

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


right-join-vs-right-outer-join-execution-properties-comparision

CONCLUSION

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

ALSO READ