tutorial net
*********WOOOOOOW *********
We're happy to see you're enjoying our races (already 5 pages viewed today)! You can keep checking out by becoming a member of the tutorial net community. It's free!
You will also be able to keep track of your race progress, practice on exercises, and chat with other members.

Share
Go down
Admin
Posts : 207
Join date : 2017-11-11
Age : 27
Location : algeria
View user profilehttp://www.tutorial-net.com

TUTORIAL SQL -What is the difference between “INNER JOIN” and “OUTER JOIN”?

on Wed Apr 11, 2018 5:33 pm
Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?

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:

Code:
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.

Code:
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 outer join

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

Code:
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 outer join

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

Code:
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

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.

Code:
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
Back to top
Permissions in this forum:
You cannot reply to topics in this forum