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:
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 OUTER JOIN
SELECT * FROM Customers C LEFT JOIN Orders O ON O.CustomerId = C.CustomerId
SELECT * FROM Customers C LEFT OUTER JOIN Orders O ON O.CustomerId = C.CustomerId
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 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
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.
- 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