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
