Category Archives: Differences

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

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

Difference between JOIN and INNER JOIN in Sql Server

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

Let us prove with examples that there is no difference between JOIN and INNER 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] LEFT OUTER JOIN vs RIGHT OUTER

JOIN and INNER JOIN produces the same result

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

JOIN

INNER JOIN

SELECT C.Name 
FROM Customers C 
  JOIN Orders O
    ON O.CustomerId = C.CustomerId

RESULT:
join

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

RESULT:
inner-join

JOIN and INNER JOIN Execution Plan Comparison

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


execution-plan-comparision

[ALSO READ] Compare Execution Plans in Sql Server 2016

JOIN and INNER JOIN produces the same performance counters

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


execution-plan-properties-comparision

CONCLUSION

From the above various examples we can see that there is absolutely NO DIFFERENCE between JOIN and INNER JOIN. Even though both produces the same result and performance, I would prefer using INNER JOIN instead of just JOIN. As it is more readable and leaves no ambiguity. If we just write JOIN instead of INNER JOIN it is not obvious what it means or what the developer was intended while writing it. Whether the Junior Developer OR the developer moved from other Database technologies while writing JOIN, he would have thought it behaves as OUTER JOIN. Just avoid such confusions and to make it more readable and obvious I would prefer writing INNER JOIN instead of JOIN.

ALSO READ

Difference between SMALLDATETIME and DATETIME Data Types in Sql Server

Both SMALLDATETIME and DATETIME Data Types in Sql Server are used for storing Date and Time values in Sql Server. Below table summarizes some of the major difference between these two Data Types.

[ALSO READ] DateTime vs DateTime2

SMALLDATETIME

DATETIME

FORMAT YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss.nnn
MIN Value 1900-01-01 00:00:00 1753-01-01 00:00:00
MAX Value 2079-06-06 23:59:00 9999-12-31 23:59:59.997
Storage Size 4 bytes 8 bytes
Usage sql-datetime-accuracy-1 datetime-usage-example
Accuracy 1 Minute

Second’s values that are 29.998 seconds or less are rounded down to the nearest minute. And second’s values of 29.999 seconds or more are rounded up to the nearest minute. So seconds part value is always 00.

Example 1:
sql-smalldatetime-seconds-accuracy-1
Example 2:
sql-smalldatetime-seconds-accuracy-2

Rounded to increments of .000, .003, or .007 second
It means:

If time part in the date is 23:59:58.990 or 23:59:58.991, it will be stored as 23:59:58.990.

Example
sql-datetime-accuracy-1

If time part in the date is 23:59:58.992 or 23:59:58.993 or 23:59:58.994, it will rounded and stored as 23:59:58.993

Example 2:
sql-datetime-accuracy-2

If time part in the date is 23:59:58.995 or 23:59:58.996 or 23:59:58.997 or 23:59:58.998, it will be rounded and stored as 23:59:58.997

If time part in the date is 23:59:58.999, it will be rounded and stored as 23:59:59.000

ALSO READ