In this post we will see differences between all SQL Server join types.

When we join two tables you need to identify what are the matching columns to include them into the JOIN clause. For this post, we will use as example the following tables T1 and T2 where the matching columns are T1.id1 and T2.id2:

 

SQL Server implements INNER JOINs and OUTER JOINs to join tables; a subset of INNER JOIN is the NATURAL JOIN that SQL Server does not implement. Regarding the OUTER JOINs SQL Server implements LEFT, RIGHT, CROSS, and FULL join. Most of them are covered in this post:

 

Inner Join

In this case SQL Server engine only returns rows that match same value (in comparison column) in both table. See example below:

go
select * from t1
inner join t2 
  on t1.id1=t2.id2;
/*
    id1         t1   id2         t2
    ----------- ---- ----------- ----
    2           t1   2           t2
*/

 

 

Left Outer Join

In this case SQL Server engine return all rows from left table (1 and 2) and fill with right table values those rows that match with the same value in comparison column. For rows in the left table that don’t match with from the right table, the values for the right table are filled with NULLs. See example below:

go
select * from t1
left join t2 
  on t1.id1=t2.id2;

/*
    id1         t1   id2         t2
    ----------- ---- ----------- ----
    1           t1   NULL        NULL
    2           t1   2           t2
*/

Note: Left Outer Join = Left Join

 

 

Right Outer Join

In this case SQL Server engine return all rows from right table (2 and 3) and fill with left table values those rows that match with the same value in comparison column. For rows in the right table that don’t match with from the left table, the values for the left table are filled with NULLs. See example below:

go
select * from t1
right join t2 
  on t1.id1=t2.id2;

/*
    id1         t1   id2         t2
    ----------- ---- ----------- ----
    2           t1   2           t2
    NULL        NULL 3           t2
*/

Note: Right Outer Join = Right Join

 

 

Full Outer Join

In this case SQL Server engine return the result for left + right outer joins. See example below:

go
select * from t1
full join t2 
  on t1.id1=t2.id2;

/*
    id1         t1   id2         t2
    ----------- ---- ----------- ----
    1           t1   NULL        NULL
    2           t1   2           t2
    NULL        NULL 3           t2
*/

 

 

All code to test in SQL Server Management Studio:

drop table t1; drop table t2;
create table t1 (id1 int, t1 char(2) default 't1');
go
create table t2 (id2 int, t2 char(2) default 't2');
go
insert t1 (id1) select 1;
insert t1 (id1) select 2;
insert t2 (id2) select 2;
insert t2 (id2) select 3;
go
select * from t1
inner join t2 
  on t1.id1=t2.id2;
/*
    id1         t1   id2         t2
    ----------- ---- ----------- ----
    2           t1   2           t2
*/

go
select * from t1
left join t2 
  on t1.id1=t2.id2;

/*
    id1         t1   id2         t2
    ----------- ---- ----------- ----
    1           t1   NULL        NULL
    2           t1   2           t2
*/

go
select * from t1
right join t2 
  on t1.id1=t2.id2;

/*
    id1         t1   id2         t2
    ----------- ---- ----------- ----
    2           t1   2           t2
    NULL        NULL 3           t2
*/

go
select * from t1
full join t2 
  on t1.id1=t2.id2;

/*
    id1         t1   id2         t2
    ----------- ---- ----------- ----
    1           t1   NULL        NULL
    2           t1   2           t2
    NULL        NULL 3           t2
*/

go
select * from t1, t2 

/*
    id1         t1   id2         t2
    ----------- ---- ----------- ----
    1           t1   2           t2
    2           t1   2           t2
    1           t1   3           t2
    2           t1   3           t2
*/

go

 

Thanks to Eladio Rincon for helping me out!

Hope that it helps!

Regards

JQ