Differences between Join, Inner Join, Left Join, Left Outer Join, Right Join, etc<!-- --> | <!-- -->Patrick Desjardins Blog
Patrick Desjardins Blog
Patrick Desjardins picture from a conference

Differences between Join, Inner Join, Left Join, Left Outer Join, Right Join, etc

Posted on: August 27, 2011

Since few months I have notice that some people near of me still do their join between table in the Where clause. It works even if it has its limitations but the biggest problem come for the maintenance. Later, a big query become hard to modify because the criterion to refine the upcoming data are mixed up with joining where clauses.

To solve this problem, it's simpler to use Join keyword for joining table between each of them. The Where clause is still important to refine the data. In this post, I will write the difference between all possible Join from a SQL perspective and write a little bit about their Linq syntax homologous. After all, we are a .Net blog!

In Sql, some statement are the same. The only difference is how it's written.

Join and Inner Join

To begin with, a query with Join is the same thing as Inner Join. What is a Join or a Inner Join? It's what people are doing with the Where clause, it compares two keys and join them only if a relationship exist. Here is a simple example with the Join done inside the Where clause:

1SELECT EmployeeTerritories.EmployeeID AS 'ET.EMPID' , EmployeeTerritories.TerritoryID AS 'ET.TID' , Territories.TerritoryID AS 'T.TID' FROM EmployeeTerritories, Territories WHERE EmployeeTerritories.TerritoryID = Territories.TerritoryID AND EmployeeTerritories.EmployeeID

These tables can be found in the Microsoft NorthWind database. The line 3 does the join when the line 4 refines the query. The database's table schema below illustrate you the relationship between the table EmployeeTerritories and Territories.

Territories 300x98

This produce :

ET.EMPID|ET.TID|T.TID

1 06897 06897 1 19713 19713 2 01581 01581 2 01730 01730 2 01833 01833 2 02116 02116 2 02139 02139 2 02184 02184 2 40222 40222 3 30346 30346 3 31406 31406 3 32859 32859 3 33607 33607

The same result is output using the Join keyword or the Inner Join.

[sql line="5"] SELECT EmployeeTerritories.EmployeeID AS 'ET.EMPID' , EmployeeTerritories.TerritoryID AS 'ET.TID' , Territories.TerritoryID AS 'T.TID' FROM EmployeeTerritories JOIN Territories ON Territories.TerritoryID = EmployeeTerritories.TerritoryID WHERE EmployeeTerritories.EmployeeID <= 3

1Or
2
3[sql line="5"] SELECT EmployeeTerritories.EmployeeID AS 'ET.EMPID' , EmployeeTerritories.TerritoryID AS 'ET.TID' , Territories.TerritoryID AS 'T.TID' FROM EmployeeTerritories INNER JOIN Territories ON Territories.TerritoryID = EmployeeTerritories.TerritoryID WHERE EmployeeTerritories.EmployeeID <= 3

Left Join and Left Outer Join

These two are also the same. In fact, the Outer keywords is implicit everytime in a SQL query.

The following query return exactly the same as the Join but it has a reason why.

[sql line="5"] SELECT EmployeeTerritories.EmployeeID AS 'ET.EMPID' , EmployeeTerritories.TerritoryID AS 'ET.TID' , Territories.TerritoryID AS 'T.TID' FROM EmployeeTerritories LEFT JOIN Territories ON Territories.TerritoryID = EmployeeTerritories.TerritoryID WHERE EmployeeTerritories.EmployeeID <= 3

1The reason is that all EmployeeTerritories have their territories defined in the Territories table. Let say that in the future a territory is deleted. The territory 19713 is deleted but not in EmployeeTerritories table. The result will be :
2
3ET.EMPID|ET.TID|T.TID
4---------------------
51 06897 06897
61 19713 NULL
72 01581 01581
82 01730 01730
92 01833 01833
102 02116 02116
112 02139 02139
122 02184 02184
132 40222 40222
143 30346 30346
153 31406 31406
163 32859 32859
173 33607 33607
18
19You can see that the T.TID return NULL. Left Join or Left Inner Join let you return all data from the Where criterion and try to Join. If data is found, it will output them, otherwise, it will return NULL. This is different from Inner Join or Join that would have simply not return this row.
20
21## Right Join or Right Outer Join
22
23The Right Join statement also known as Right Outer Join is the mirror of the Left Outer Join. That mean that all results are from from the Joined table and then the join is try. If no data is found, NULL is returned.
24
25## Cross Join
26
27Cross Join is a Cartesian product. That mean that all data from the left side and join them to the right. The previous example with the Where statement that is query to '<=3' has 3 EmployeeTerritories. The Join Territories table contains 53 territories. If we execute a Group Statement to know how many Territories the EmployeeTerritories have per EmployeeID this will give you the number to multiply to 53 to know the amount or row returned.
28
29
30```sql
31 SELECT EmployeeID, count(TerritoryID) FROM EmployeeTerritories WHERE EmployeeTerritories.EmployeeID <= 3 GROUP BY EmployeeID

EmployeeID|count(TerritoryID)

1 2 2 7 3 4

This give 13 totals amount of Employees/Territories that will be Cross Joined to 53 territories. For a total of 689 rows. Here is the Cross Join query:

1SELECT distinct(EmployeeTerritories.EmployeeID) AS 'ET.EMPID' , EmployeeTerritories.TerritoryID AS 'ET.TID' , Territories.TerritoryID AS 'T.TID' FROM EmployeeTerritories CROSS JOIN Territories WHERE EmployeeTerritories.EmployeeID <=3

As you can see, the CROSS JOIN does not require to have the ON keyword because all rows are joined.

Full Join and Full Outer Join

This join is combining the Left Join and Right Join. In fact, even some database does not support Full Join (like SqlLite) but Microsoft SQL Server does.

The same query modified for Full Join return 13 rows also.

1SELECT EmployeeTerritories.EmployeeID AS 'ET.EMPID' , EmployeeTerritories.TerritoryID AS 'ET.TID' , Territories.TerritoryID AS 'T.TID' FROM EmployeeTerritories FULL JOIN Territories ON Territories.TerritoryID = EmployeeTerritories.TerritoryID WHERE EmployeeTerritories.EmployeeID <= 3

This return the same thing because as the Left and Right example, it had always a joined value on the other table. But with a Full Join, it can be possible to see returned value with NULL in the left or in the right column. Result could be like this:

1 - 2 NULL - 3 4 - NULL

Conclusion

All those Joins can be executed in Linq. Most of them have a similar syntax except the Full Join that is more tricky. In an other post, default will be provided for all those joins.