top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Real life example for Outer join and Inner join.

+3 votes
1,190 views

Can any one explain me both the joins with reat life examples.

posted Jun 19, 2014 by Khusboo

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

An inner join of A and B gives the result of A intersect B while outer join of A and B gives the results of A union B.

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

An inner join using either of the equivalent queries gives the intersection of the two 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

Outer join

A 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
answer Jun 20, 2014 by Amit Kumar Pandey
...