OUTER JOIN in T-SQL in SQL Server 2012

In this topic I show You how work outer joins in T-SQL. It is three type of this joins:LEFT JOIN, RIGHT JOIN and FULL JOIN.

Begin with LEFT JOIN.

use mybase;

create table tab10 
(
	id int primary key,
	name varchar(100)
);

create table tab20 
(
	id int primary key,
	name varchar(100) ,
	if_is bit
);

create table tab30
(
	id int primary key,
	name varchar(100) 
);

insert into tab10 values(1,'one');
insert into tab10 values(2,'two');
insert into tab10 values(3,'three');
insert into tab10 values(4,'four');
insert into tab10 values(5,null);

insert into tab20 values(1,'one',0);
insert into tab20 values(2,'two',1);
insert into tab20 values(3,'three',1);
insert into tab20 values(4,'cztery',1);
insert into tab20 values(5,null,0);

insert into tab30 values(1,'one');
insert into tab30 values(2,'two');
insert into tab30 values(3,'three');
insert into tab30 values(4,null);
insert into tab30 values(5,null);

When You get all data from these tables You see:

SELECT * FROM tab10;
SELECT * FROM tab20;
SELECT * FROM tab30;

z1
The LEFT JOIN  get all data from one table ( this after FROM clause) and adjust to them records from second table(this given in LEFT JOIN clause).
It is first example for LEFT JOIN:

SELECT tab10.name  tab10_name, 
       tab20.name tab20_name
FROM tab10 
LEFT JOIN tab20 
ON tab10.name = tab20.name;

Result:
a1
And second example for LEFT JOIN:

SELECT tab20.name  tab20_name, 
       tab30.name tab30_name
FROM tab20 
LEFT JOIN tab30 
ON tab20.name = tab30.name;

Result:
a2
This join shows either records from first table that have not adjustment in second table.But records in second table without adjustment in first table is not show.

It is first example for RIGHT JOIN:

SELECT tab10.name  tab10_name, 
       tab20.name tab20_name
FROM tab10 
RIGHT JOIN tab20 
ON tab10.name = tab20.name;

Result:
a3
And second example for RIGHT JOIN:

SELECT tab20.name  tab20_name, 
       tab30.name tab30_name
FROM tab20 
RIGHT JOIN tab30 
ON tab20.name = tab30.name;

Result:
a4

This join shows either records from second table that have not adjustment in first table. But records in first table without adjustment in second table is not show.

It is first example for FULL JOIN:

SELECT tab10.name  tab10_name, 
       tab20.name tab20_name
FROM tab10 
FULL JOIN tab20 
ON tab10.name = tab20.name;

Result:
a6
And second example for FULL JOIN:

SELECT tab20.name  tab20_name, 
       tab30.name tab30_name
FROM tab20 
FULL JOIN tab30 
ON tab20.name = tab30.name;

Result:
a7

This join showsall records from first and second table, either that have not adjustment in another table.

Of course, You may use in ON expression complex conditions.

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.