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

Example

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

Sample Database

create table customerType (
	CustomerTypeID int primary key, 
	Name varchar(10)
)

insert into customerType values (1,'Patron')
insert into customerType values (2,'VIP')
insert into customerType values (3,'Regular')


create table Customers (
   CustomerID int primary key,
   Name varchar(100),
   CustomerTypeID int null,
   CONSTRAINT FK_Customers_CustomerTypeID FOREIGN KEY (CustomerTypeID)
      REFERENCES customerType (CustomerTypeID)
);

insert into Customers values(1, 'Kevin Costner',1);
insert into Customers values(2, 'Akshay Kumar',2);
insert into Customers values(3, 'Sean Connery',1);
insert into Customers values(4, 'Sanjay Dutt',2);
insert into Customers values(5, 'Sharukh Khan',null);


create table Orders (
   OrderNo int primary key,
   OrderDate datetime,
   CustomerID int null,
   Amount decimal(10,2),
   CONSTRAINT FK_Orders_CustomerID FOREIGN KEY (CustomerID)
      REFERENCES Customers (CustomerID)
);

insert into Orders Values(1,'2019-12-10',1,5000)
insert into Orders Values(2,'2019-12-09',1,3000)
insert into Orders Values(3,'2019-12-10',2,7000)
insert into Orders Values(4,'2019-12-01',2,7000)
insert into Orders Values(8,'2019-12-10',2,4000)
insert into Orders Values(5,'2019-12-10',3,1000)
insert into Orders Values(6,'2019-12-03',3,1000)
insert into Orders Values(7,'2019-12-10',4,3000)

Join Query

Inner Join

select c.name, t.name as customerType
from customers c
join customerType t
on (c.custmerTypeid=t.customerTypeid)

Left Join

select c.name, t.name as customerType
from customers c
join customerType t
on (c.custmerTypeid=t.customerTypeid)

Right Join

select c.name, t.name as customerType
from customers c
right join customerType t
on (c.custmerTypeid=t.customerTypeid)

Full Join

select c.name, t.name as customerType
from customers c
full join customerType t
on (c.custmerTypeid=t.customerTypeid)

Query Result

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.

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

select c.name, t.name as customerType, o.OrderDate, o.Amount
from customers c
join customerType t
on (c.customerTypeid=t.customerTypeid)
join Orders o
on (o.CustomerID=c.CustomerID)

Or
select c.name, t.name as customerType, o.OrderDate, o.Amount
from Orders o
join Customers c
on (o.CustomerID=c.CustomerID)
join customerType t
on (c.customerTypeid=t.customerTypeid)

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.