Difference between inner & outer join (left, right and full)

In this article, we will explore the differences between inner & outer join in SQL Server. The join statement in the SQL server is used to join data from two or more tables. There are a few different types of joins in SQL Server. Understanding the difference between them is very crucial in writing better SQL Querries

Differences between inner join & outer join

The following Venn diagram clearly shows the difference between each join type. There are three kinds of joins in SQL Server, inner, outer and cross. The outer join is further divided as left, right & full.

INNER JOIN: Returns only matched rows

LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

FULL JOIN: Return all rows from both the tables

 InnerLeft
Outer
Right
Outer
Full
Outer
Matching RowsYesYesYesYes
Left TableNoYesNoYes
Right TableNoNoYesYes
Join Order
of the tables
Does not
matter
does
matter
does
matter
Does not
matter

Example

Let us compare the result of all the join types side by side.

Sample Database

Join Query

Inner Join

Left Join

Right Join

Full Join

Query Result

Comparison of Query results of inner and various outer joins like left, right and full
Comparison of Query results of inner and various outer joins like left, right and full

From the above, you can see the difference in each result. All Query results include the matching rows. The left join has all the rows from the customer table & right join has all the rows from the customer type table. While full join has all the rows from both the tables.

Join Order of tables

Now, you move the CustomerType table to the from clause and the Customers table to the join clause. The result of inner and full join does not change, while that of left and right changes. In fact, the result of left join and right join gets exchanged.

Comparison of Query results with join order of tables reversed
Comparison of Query results with the join order of tables reversed

Join Order in more than 3 tables

The following is more than 3 tables joined using only inner join. The result of both the queries are the same

Similarly, the full join also results in the same result.

While left & right produce different results as you change the join order of tables.

References

  1. Join Syntax (SQL Server)
  2. Sample database

Summary

We learned the difference between inner & outer joins like left, right & full join with example. Understanding the difference between joins is crucial in writing better queries in SQL Server.

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top