http://stackoverflow.com/questions/38549/sql-difference-between-inner-and-outer-join
Assuming you’re joining on columns with no duplicates, which is by far the most 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
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
Left outer join
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
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.
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
link|improve this answer
edited Mar 12 at 18:18
Peter Mortensen
4,35142051
answered Sep 1 ’08 at 22:59
Mark Harrison
31.4k23120192
51
Excellently explained – HLGEM Nov 24 ’08 at 22:22
42
if u write a book i’ll buy a copy – masato-san Jan 20 ’11 at 0:46
5
One of the best explanations I have seen! – Lea Hayes Apr 25 ’11 at 16:35
14
An excellent explanation, however this statement: An outer join of A and B gives the results of A union B, i.e. the outer parts of a venn diagram union. isn’t phrased accurately. An outer join will give the results of A intersect B in addition to one of the following: all of A (left join), all of B (right join) or all of A and all of B (full join). Only this last scenario is really A union B. Still, a well written explanation. – Thomas May 3 ’11 at 19:57
4
Simple explanations are often the best. This FAR EXCEEDS the Wikipedia on joins haha. Truly excellent examples with just enough data to see a clear picture. Thanks Mark 🙂 – Chiramisu Aug 2 ’11 at 18:26
show 8 more comments
feedback
up vote
36
down vote
I recommend Jeff’s blog article. The best description I’ve ever seen, plus there is a visualization, e.g.:
Inner Join:
Full Outer Join:
link|improve this answer
edited Dec 11 ’11 at 9:47
Bragboy
8,29841455
answered Aug 30 ’09 at 11:52
ya23
2,1822026
3
This diagram is a bit misleading for the concept. Read the comments in the post as well. – Pratik Aug 30 ’09 at 12:56
feedback
up vote
18
down vote
The following was taken from the article “MySQL – LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN” by Graham Ellis on his blog Horse’s Mouth.
In a database such as MySQL, data is divided into a number of tables which are then connected (Joined) together by JOIN in SELECT commands to read records from multiple tables. Read this example to see how it works.
First, some sample data:
demo_people
mysql> select * from demo_people;
+————+————–+——+
| name | phone | pid |
+————+————–+——+
| Mr Brown | 01225 708225 | 1 |
| Miss Smith | 01225 899360 | 2 |
| Mr Pullen | 01380 724040 | 3 |
+————+————–+——+
3 rows in set (0.00 sec)
demo_property
mysql> select * from demo_property;
+——+——+———————-+
| pid | spid | selling |
+——+——+———————-+
| 1 | 1 | Old House Farm |
| 3 | 2 | The Willows |
| 3 | 3 | Tall Trees |
| 3 | 4 | The Melksham Florist |
| 4 | 5 | Dun Roamin |
+——+——+———————-+
5 rows in set (0.00 sec)
WITHOUT JOIN
If we do a regular JOIN (with none of the keywords INNER, OUTER, LEFT or RIGHT), then we get all records that match in the appropriate way in the two tables, and records in both incoming tables that do not match are not reported:
mysql> select name, phone, selling
from demo_people join demo_property
on demo_people.pid = demo_property.pid;
+———–+————–+———————-+
| name | phone | selling |
+———–+————–+———————-+
| Mr Brown | 01225 708225 | Old House Farm |
| Mr Pullen | 01380 724040 | The Willows |
| Mr Pullen | 01380 724040 | Tall Trees |
| Mr Pullen | 01380 724040 | The Melksham Florist |
+———–+————–+———————-+
4 rows in set (0.01 sec)
LEFT JOIN
If we do a LEFT JOIN, we get all records that match in the same way and IN ADDITION we get an extra record for each unmatched record in the left table of the join – thus ensuring (in this example) that every PERSON gets a mention:
mysql> select name, phone, selling
from demo_people left join demo_property
on demo_people.pid = demo_property.pid;
+————+————–+———————-+
| name | phone | selling |
+————+————–+———————-+
| Mr Brown | 01225 708225 | Old House Farm |
| Miss Smith | 01225 899360 | NULL < select name, phone, selling
from demo_people right join demo_property
on demo_people.pid = demo_property.pid;
+———–+————–+———————-+
| name | phone | selling |
+———–+————–+———————-+
| Mr Brown | 01225 708225 | Old House Farm |
| Mr Pullen | 01380 724040 | The Willows |
| Mr Pullen | 01380 724040 | Tall Trees |
| Mr Pullen | 01380 724040 | The Melksham Florist |
| NULL | NULL | Dun Roamin |
+———–+————–+———————-+
5 rows in set (0.00 sec)
An INNER JOIN does a full join, just like the first example, and the word OUTER may be added after the word LEFT or RIGHT in the last two examples – it’s provided for ODBC compatibility and doesn’t add an extra capabilities.
On the Road With Imagination 