�ݺ�ߣ

Left Join
A step by step
(V21071205.02)

Intro
Sometimes Outer Join is not so intuitive. I asked myself: how can I visually represent
the join? This presentation shows how I tried to picture the left join.

Outline
I��ll show you step by step how the Left Outer Join of 4 tables works. In
simple way you can build the Right Outer Join and the Full Outer Join.

Who is Andrea (*)
�� Start in October 1999 with Oracle 8.0.5 (who remember the pre-Java
installation?)
�� Responsible for backup of VAS datacenter and Oracle DBA until 2002
�� Developer of SQL and PL/SQL and responsible for maintenance of the
application schemas from 2002 to 2010
�� Since 2010, full time DBA: installation, configuration, migration, backup
and recovery, tuning analysis of rdbms and SQL code
�� RAC awere
(*) Male name in Italy

About me
�� Member of OraPub community: http://http://www.orapub.com
�� Active member of the ITOUG: http://www.itoug.it
�� Writer of the OraSal blog: https://orasal.blogspot.it
�� LinkedIn: https://www.linkedin.com/in/orasal
�� Twitter: @oraize
�� eMail: andrea.salzano@gmail.com

Agenda
�� Building the environment
�� The classic way
�� A��.(different) way
�� The visual way

Environment
�� The creation of 4 tables

Create tables...
create table t1 as select rownum c1 from dba_tables where rownum <11;
create table t2 as select c1+1 c2 from t1;

...tables created
select * from t1;
C1
----------
1
2
3
4
5
6
7
8
9
10
select * from t2;
C2
----------
2
3
4
5
6
7
8
9
10
11
select * from t3;
C3
----------
3
4
5
6
7
8
9
10
11
12
select * from t4;
C4
----------
4
5
6
7
8
9
10
11
12
13

Definitions
The Join between two tables return the rows
matching both tables. For example, if you
consider the tables T1 and T2, defined in the
previous slide, the Join (called also Inner
Join) will return the values from 2 to 10
It��s the same

The Outer Join, force the no matching rows to be in output. Based on the
word used, Left, Right or Full, in the output of the Join will show the rows
of the left, right or both tables
Definitions

Definitions
This syntax ... ...is the same
Inner Join Join
Left Outer Join Left Join
Right Outer Join Right Join
Full Outer Join Full Join

Left Join: way 01
�� The first way is a classic step by step outer join

T1 Left Join T2
select * from t1 left join t2 on (t1.c1=t2.c2);
C1 C2
---------- ----------
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
1
select * from t1;
C1
----------
1
2
3
4
5
6
7
8
9
10
select * from t2;
C2
----------
2
3
4
5
6
7
8
9
10
11
Joining T1 and T2 tables
T1 -> LJ -> T2
In the Left Join the
columns of the ��left��
table is mandatory

C2 C3
---------- ----------
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
2
select * from t2;
C2
----------
2
3
4
5
6
7
8
9
10
11
select * from t3;
C3
----------
3
4
5
6
7
8
9
10
11
12
T2 Left Join T3
T2 -> LJ -> T3
table is mandatory

C3 C4
---------- ----------
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12
3
select * from t3;
C3
----------
3
4
5
6
7
8
9
10
11
12
select * from t4;
C4
----------
4
5
6
7
8
9
10
11
12
13
T3 -> LJ -> T4
table is mandatory
T3 Left Join T4

select *
from t1 left join t2
on (t1.c1=t2.c2);
C1 C2
---------- ----------
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
1
select *
on (t2.c2=t3.c3);
C2 C3
---------- ----------
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
2
select *
from t1 left join t2 on (t1.c1=t2.c2)
left join t3 on (t2.c2=t3.c3);
(T1 LJ T2 ) -> LJ T3
C1 C2 C3
---------- ---------- ----------
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 8 8
9 9 9
10 10 10
1
2 2
T1 Left Join T2 Left Join T3
Final result
Joining (T1 and T2) and T3 tables

t1 left join t2 on (t1.c1=t2.c2)
select *
on (t1.c1=t2.c2);
C1 C2
---------- ----------
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
1
Common values
Step one: T1 LJ T2 => LJT12

ljt12 left join t3 on (ljt12.c2=t3.c3)
LJT12
C1 C2
---------- ----------
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
1
T3
C3
----------
3
4
5
6
7
8
9
10
12
T1 LJ T2
C1 C2
---------- ----------
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
1
2 2
T3
C3
----------
3
4
5
6
7
8
9
10
12
Step two: LJT12 LJ T3

Which is the difference? Is the result different?

left join t3 on (t2.c2=t3.c3)

Which is the difference? Is the result different?
>>> NO <<<

T1 LJ T2
C1 C2
---------- ----------
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
1
2 2
T3
C3
----------
3
4
5
6
7
8
9
10
12
T1 LJ T2
C1 C2
---------- ----------
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
1
2 2
T3
C3
----------
3
4
5
6
7
8
9
10
12
Step two: LJT12 LJ T3

T1 LJ T2
C1 C2
---------- ----------
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
1
2 2
T3
C3
----------
3
4
5
6
7
8
9
10
12
T1 LJ T2 ->
C1 C2
---------- ----------
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
1
2 2
LJ T3
C3
----------
3
4
5
6
7
8
9
10select *
left join t3 on (t2.c2=t3.c3);
(T1 Left Join T2) Left Join T3
Step three: T1 LJ T2 LJ T3

(T1 LJ T2) ->
C1 C2
---------- ----------
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
1
2 2
LJ T3
C3
----------
3
4
5
6
7
8
9
10
select *
left join t4 on (t3.c3=t4.c4;
((T1 LJ T2 ) -> LJ T3) -> LJ T4)
T4
C4
----------
4
5
6
7
8
9
10
11
12
13
(T1 LJ T2) ->
C1 C2
---------- ----------
4 4
5 5
6 6
7 7
8 8
9 9
10 10
1
2 2
3 3
LJ T3
C3
----------
4
5
6
7
8
9
10
3
T4
C4
----------
4
5
6
7
8
9
10
11
12
13
((T1 Left Join T2) Left Join T3) Left Join T4
Joining ((T1 and T2) and T3) and T4 tables

select *
((T1 LJ T2 ) -> LJ T3) -> LJ T4)
(T1 LJ T2) ->
C1 C2
---------- ----------
4 4
5 5
6 6
7 7
8 8
9 9
10 10
1
2 2
3 3
LJ T3
C3
----------
4
5
6
7
8
9
10
3
T4
C4
----------
4
5
6
7
8
9
10
11
12
13
(T1 LJ T2) ->
C1 C2
---------- ----------
4 4
5 5
6 6
7 7
8 8
9 9
10 10
1
2 2
3 3
LJ T3
C3
----------
4
5
6
7
8
9
10
3
T4
C4
----------
4
5
6
7
8
9
10
11
12
13

select *
((T1 LJ T2 ) -> LJ T3) -> LJ T4)
((T1 LJ T2) ->
C1 C2
---------- ----------
4 4
5 5
6 6
7 7
8 8
9 9
10 10
1
2 2
3 3
LJ T3) ->
C3
----------
4
5
6
7
8
9
10
3
LJ T4
C4
----------
4
5
6
7
8
9
10
(T1 LJ T2) ->
C1 C2
---------- ----------
4 4
5 5
6 6
7 7
8 8
9 9
10 10
1
2 2
3 3
LJ T3
C3
----------
4
5
6
7
8
9
10
3
T4
C4
----------
4
5
6
7
8
9
10
11
12
13

Left Join: way 02
�� The second way is an idea I had studing the Left Join

T1 LJ T4
T1
C1
----------
1
2
3
4
5
6
7
8
9
10
T2
C2
----------
2
3
4
5
6
7
8
9
10
11
T3
C3
----------
3
4
5
6
7
8
9
10
11
12
T4
C4
----------
4
5
6
7
8
9
10
11
12
13

T2 LJ T4
T1^
C1
----------
4
5
6
7
8
9
10
1
2
3
T2
C2
----------
2
3
4
5
6
7
8
9
10
11
T3
C3
----------
3
4
5
6
7
8
9
10
11
12
T4
C4
----------
4
5
6
7
8
9
10
11
12
13

T3 LJ T4
T1^
C1
----------
4
5
6
7
8
9
10
1
2
3
T2^
C2
----------
4
5
6
7
8
9
10
11
2
3
T3
C3
----------
3
4
5
6
7
8
9
10
11
12
T4
C4
----------
4
5
6
7
8
9
10
11
12
13

T1 LJ T2 LJ T3 LJ T4 (middle)
T1^
C1
----------
4
5
6
7
8
9
10
1
2
3
T2^
C2
----------
4
5
6
7
8
9
10
11
2
3
T3^
C3
----------
4
5
6
7
8
9
10
11
12
3
T4^
C4
----------
4
5
6
7
8
9
10
11
12
13

T1^ LJ T2^
T1^
C1
----------
4
5
6
7
8
9
10
1
2
3
T2^
C2
----------
4
5
6
7
8
9
10
11
2
3
T3^
C3
----------
4
5
6
7
8
9
10
11
12
3
T4^
C4
----------
4
5
6
7
8
9
10
11
12
13

T1^
C1
----------
4
5
6
7
8
9
10
1
2
3
T2
C2
----------
4
5
6
7
8
9
10
2
3
T3^
C3
----------
4
5
6
7
8
9
10
11
12
3
T4^
C4
----------
4
5
6
7
8
9
10
11
12
13
T1^ LJ T3^

T1^
C1
----------
4
5
6
7
8
9
10
1
2
3
T2
C2
----------
4
5
6
7
8
9
10
2
3
T3
C3
----------
4
5
6
7
8
9
10
3
T4^
C4
----------
4
5
6
7
8
9
10
11
12
13
T1^ LJ T4^

T1
C1
----------
4
5
6
7
8
9
10
1
2
3
T2
C2
----------
4
5
6
7
8
9
10
2
3
T3
C3
----------
4
5
6
7
8
9
10
3
T4
C4
----------
4
5
6
7
8
9
10
T1 LJ T2 LJ T3 LJ T4

Compare
T1
C1
----------
4
5
6
7
8
9
10
1
2
3
T2
C2
----------
4
5
6
7
8
9
10
2
3
T3
C3
----------
4
5
6
7
8
9
10
3
T4
C4
----------
4
5
6
7
8
9
10
((T1 LJ T2) ->
C1 C2
---------- ----------
4 4
5 5
6 6
7 7
8 8
9 9
10 10
1
2 2
3 3
LJ T3) ->
C3
----------
4
5
6
7
8
9
10
3
LJ T4
C4
----------
4
5
6
7
8
9
10

Left Join: way 03
�� The third way is a Visual representation of the outer join (see the
reference section)

Visual T1 Left Join T2
select * from t1 left join t2 on (t1.c1=t2.c2); select *
from t1;
C1
----------
1
2
3
4
5
6
7
8
9
10
select *
from t2;
C2
---------
-
2
3
4
5
6
7
8
9
10
11
NULL,
2,3,4,5,
6,7,8,9,
10
1,
2,3,4,5,
6,7,8,9,
10
1,2,3,4,
5,6,7,8,
9,10
2,3,4,5,
6,7,8,9,
10,11
select *
from t1 left join t2 on (t1.c1=t2.c2);
C1 C2
---------- ----------
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
1 NULL

from t2;
C2
----------
2
3
4
5
6
7
8
9
10
11
select *
from t3;
C3
----------
3
4
5
6
7
8
9
10
11
12
NULL,
3,4,5,6,
7,8,9,10
,11
2
,3,4,5,6
,7,8,9,
10,11
2,3,4,5,
6,7,8,9,
10,11
3,4,5,6,
7,8,9,10
,11,12
select *
C2 C3
---------- ----------
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
2 NULL

from t3;
C3
----------
3
4
5
6
7
8
9
10
11
12
select *
from t4;
C4
----------
4
5
6
7
8
9
10
11
12
13
NULL,
3,4,5,6,
7,8,9,10
,11,12
3,
4,5,6,7,
8,9,
10,11,12
3,4,5,6,
7,8,9,10
,11,12
3,4,5,6,
7,8,9,10
,11,12,13
select *
C3 C4
---------- ----------
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12
3 NULL

select *
from t1 left join t2 on
(t1.c1=t2.c2)
left join t3 on
(t2.c2=t3.c3);
Visual T1 Left Join T2 Left Join T3
1,2,3,4,
5,6,7,8
,9,10
2,3,4,
5,6,7,8
,9,10,11
NULL,
2,3,4,5,
6,7,8,9,
10
1,
2,3,4,5,
6,7,8,9,
10
NULL,
2,3,4,5,
6,7,8,9,
10
1,
2,3,4,5,
6,7,8,9,
10
3,4,5,6,
7,8,9,
10,11,12
NULL,
NULL,
3,4,5,6,
7,8,9,10
NULL,
2,3,4,5,
6,7,8,9,
10
1,
2,3,4,5,
6,7,8,9,
10
C1 C2 C3
---------- ---------- ----------
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 8 8
9 9 9
10 10 10
1 NULL NULL
2 2 NULL

select *
from t1 left join t2 on
(t1.c1=t2.c2)
left join t3 on
(t2.c2=t3.c3)
left join t4 on
(t3.c3=t4.c4);
Visual T1 Left Join T2 Left Join T3 Join T4
1,2,3,4,
5,6,7,8
,9,10
2,3,4,
5,6,7,8
,9,10,11
NULL,
2,3,4,5,
6,7,8,9,
10
1,
2,3,4,5,
6,7,8,9,
10
NULL,
2,3,4,5,
6,7,8,9,
10
1,
2,3,4,5,
6,7,8,9,
10
3,4,5,6,
7,8,9,
10,11,12
NULL,
NULL,
3,4,5,6,
7,8,9,10
NULL,
2,3,4,5,
6,7,8,9,
10
1,
2,3,4,5,
6,7,8,9,
10
NULL,
NULL,
3,4,5,6,
7,8,9,10
NULL,
2,3,4,5,
6,7,8,9,
10
1,
2,3,4,5,
6,7,8,9,
10
4,5,6,7,
8,9,
10,11,12
,13
NULL,
NULL,
NULL,
4,5,6,7,
8,9, 10
NULL,
NULL,
3,4,5,6,
7,8,9,10
NULL,
2,3,4,5,
6,7,8,9,
10
1,
2,3,4,5,
6,7,8,9,
10
C1 C2 C3 C4
---------- ---------- ----------
----------
4 4 4 4
5 5 5 5
6 6 6 6
7 7 7 7
8 8 8 8
9 9 9 9
10 10 10 10
1 NULL NULL NULL
2 2 NULL NULL
3 3 3 NULL

References
�� https://www.techonthenet.com/oracle/joins.php
�� https://www.w3schools.com/sql/sql_join_left.asp

�ݺ�ߣ

Left outer join step by step

More Related Content

Left outer join step by step