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

## No comments:

## Post a Comment