SQL Database Joining Tutorial. Inner join, Left join, Right join, Full outer join.

Hallo All.

As a student of Database I realize that maximum student have some difficulty to understand JOINNING in SQL.. Now I am trying to make a little tutorial example to understand JOINNING. Hope you can understand what is joining and how it works. Let’s start…

Assuming you're joining on columns with no duplicates, which is a very common case:

·         An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram  intersection.

·         An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples:

Suppose you have two tables, with a single column each, and data as follows:

A    B

-    -

1    3

2    4

3    5

4    6

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;

select a.*,b.*  from a,b where a.a = b.b;

a | b

--+--

3 | 3

4 | 4

Left join

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;

select a.*,b.*  from a,b where a.a = b.b(+);

a |  b

--+-----

1 | null

2 | null

3 |    3

4 |    4

Right join

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

 A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;

select a.*,b.*  from a,b where a.a(+) = b.b;

a    |  b

-----+----

3    |  3

4    |  4

null |  5

null |  6

Full outer join

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2). The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b

-----+-----

   1 | null

   2 | null

   3 |    3

   4 |    4

null |    6

null |    5