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:
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
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 OUTER JOIN
SELECT * FROM Customers C RIGHT JOIN Orders O ON O.CustomerId = C.CustomerId
SELECT * FROM Customers C RIGHT OUTER JOIN Orders O ON O.CustomerId = C.CustomerId
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 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
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.
- Varchar vs NVarchar
- Varchar vs Varchar(MAX)
- Char vs Varchar
- Text vs Varchar(Max)
- Union vs Union All
- DateTime vs DateTime2
- SET QUOTED_IDENTIFIER ON vs SET QUOTED_IDENTIFIER OFF
- Stored Procedure vs User Defined Function
- Primary Key vs Unique Key
- RAISERROR vs THROW
- Temporary Table vs Table Variable
- Len() vs Datalength()
- Sequence vs Identity
- DATEDIFF vs DATEDIFF_BIG
- LEFT JOIN vs LEFT OUTER JOIN
- RIGHT JOIN vs RIGHT OUTER JOIN
- JOIN vs INNER JOIN
- LEFT OUTER JOIN vs RIGHT OUTER JOIN
- SMALLDATETIME vs DATETIME