Re: difference between inner and outer joins

Rob,

It's difficult to be simple with something like joins, but here's my humble try:

Example 1
An Inner Join will show only records that are related between the two joined tables. Let's say you're joining the tables on an ID field. In Table 1 you have IDs of "10", "20", and "30". In Table 2, you have IDs of "20" and "40". An Inner Join would show you only the record with the ID of "20" as that is the only matching field between the two tables. Like so:

Table 1        Table 2
20                20

Example 2
An Outer Join will show records even if they don't have related records in the joined table, therefore to use the same example, if you did an Outer Join from Table 1, you would get records "10", "20", and "30" as you're showing ALL of the records from Table 1. Since you are only showing MATCHING records from Table 2, the only ID in Table 1 that would match against Table 2 would be "20". Like so:

Table 1 Table 2
10

20                20

Example 3
An Outer Join from Table 2 instead of Table 1 would show all records from Table 2, "20" and "40", and only "20" would have a matching value in Table 1. "40" would match to a blank record. Like so:

Table 1        Table 2
20                20
                    40

In above Examples 2, and 3, you are seeing examples of a Left Outer Join and a Right Outer Join, the principle is the same, the difference is which table you're doing the Outer Join on. Don't worry about this for a basic understanding - yet.

Also, look out for Cartesian Products. And no, Cartesian Products aren't Souveniers from the country Cartesia. :)

David
www.davidbase.com

---


> Hi all: I'm a systems analyst student currently doing some relational

> work with oracle and (on the side) mysql.  I can't for the life of me

> figure out what the difference between an inner and outer join is?!

> Can anyone explain this to me.  Please over-simplify as much as


> possible :)