- Sunday, 24 July 2005 23:21
There are 5 types of joins in SQL:
1. outer join
2. inner join
3. left join
4. right join
5. cross join
example:
table: t_a
ID : 1, 2, 3, 4, 5
table: t_b
ID: 3, 4, 5, 6, 7
-----------------------------
OUTER JOIN : keep both tables' data
if there is no match, fill with NULL
-----------------------------
select t_a.ID as IDA, t_b.ID as IDB
from t_a, t_b
where t_a.ID OUTER_JOIN t_b.ID
result ==>
IDA IDB
1 NULL
2 NULL
3 3
4 4
5 5
NULL 6
NULL 7
-----------------------------
INNER JOIN :
if there is no match, don't return it
-----------------------------
select t_a.ID as IDA, t_b.ID as IDB
from t_a, t_b
where t_a.ID INNER_JOIN t_b.ID -- or t_a.ID = t_b.ID
result ==>
IDA IDB
3 3
4 4
5 5
-----------------------------
LEFT JOIN : keep left table's data
if there is no match, fill with NULL
-----------------------------
select t_a.ID as IDA, t_b.ID as IDB
from t_a, t_b
where t_a.ID LEFT_JOIN t_b.ID
result ==>
IDA IDB
1 NULL
2 NULL
3 3
4 4
5 5
-----------------------------
RIGHT JOIN : keep right tables' data
if there is no match, fill with NULL
-----------------------------
select t_a.ID as IDA, t_b.ID as IDB
from t_a, t_b
where t_a.ID RIGHT_JOIN t_b.ID
result ==>
IDA IDB
3 3
4 4
5 5
NULL 6
NULL 7
-----------------------------
CROSS JOIN : basically it is a cross product
very expensive
-----------------------------
select t_a.ID as IDA, t_b.ID as IDB
from t_a, t_b
where t_a.ID CROSS_JOIN t_b.ID
result ==>
IDA IDB
1 3
1 4
1 5
1 6
1 7
2 3
2 4
2 5
2 6
2 7
3 3
3 4
3 5
3 6
3 7
4 3
4 4
4 5
4 6
4 7
5 3
5 4
5 5
5 6
5 7
