Inner and Outer Joins

[Eddie Long]

Please explain the difference between these two type of joins. I can't seem to understand it

[Jeanne Boyarsky]

Eddie,
An inner join requires both fields to be the same. A full outer join requires the fields to be the same or one of them to be null. There are also left/right outer joins that only check one field for nulls.

Suppose, I have the following data:

table1/field    table2/field
A               A
null            A
A               null

An inner join only returns the first row. A full outer join returns all nine combinations of the three rows.

[David Harkness]

Outer joins come into play when you want to return rows even when the join cannot match values on either one side or the other or both, as Jeanne explained.

An example would be a product table (with PK product_id) and total_sales table ( FK product_id, number total ) where products without sales have no row in total_sales (as opposed to a row with a zero total).

If you want to list all product IDs and their total sales whether or not they have any, you'd need to do an outer join on product_id. Every time I use outer joins I need to either look up the syntax or try it several times to get it right, but I think this is correct (for Oracle):

select p.product_id, s.total
from   product p, total_sales s
where  p.product_id = s.product_id (+) ;

This tells the SQL query engine that, for any p.product_id without a matching s.product_id, create a fake s row containing all null values. Therefore s.total will be null. Here's some sample output whee the second product has no matching total_sales row:

PRODUCT_ID TOTAL_SALES
---------- -----------
123        12
456        NULL
789        47

[Avi Abrami]

Jeanne,

An inner join only returns the first row. A full outer join returns all nine combinations of the three rows

And a left outer join would return ...?
And a right outer join would return ...?
[For the sake of completeness, please.]

David,
Since Oracle 9i, Oracle SQL does support the ANSI syntax -- using "inner join", "outer join", etc. I believe it would be better not to give the impression that Oracle still uses a proprietary syntax for "outer join"s.

Good Luck,
Avi.

[David Harkness]

Originally posted by Avi Abrami:
Since Oracle 9i, Oracle SQL does support the ANSI syntax -- using "inner join", "outer join", etc. I believe it would be better not to give the impression that Oracle still uses a proprietary syntax for "outer join"s.

I'd have avoided giving that impression had I know it myself.

Okay, that was more for the joke value, but I must say I've only barely skimmed the ISO 99 syntax and never used it. I've used Oracle on every project and job since 1999 and thus haven't had a chance to try the new syntax (no need). And as I said, I rarely need outer joins for the type of applications I build.

So I may as well take this opportunity to get my hands dirty.

select p.product_id, s.total
from   product p
left outer join total_sales s
  on   (p.product_id = s.product_id) ;

I suppose that is a little easier to understand. Not so pretty with my preferred indentated format, but I'll live. I'll have to see with some of the multiple-table outer joins I have. I had to code up one of them using a subquery to get it to work right; that'll make a good exercise.

Thanks, Avi, for the nudge.

[Jeanne Boyarsky]

Originally posted by Avi Abrami:
Jeanne,

And a left outer join would return ...?
And a right outer join would return ...?
[For the sake of completeness, please.]

A left join would return the six combinations where the left column has the value "A". Similarly, a right join would return the six combinations where the right column has the value "A."

[David Harkness]

Oracle didn't agree with any of the results presented here (inner, full outer, left outer or right outer). I suspect that's because the sample data was a little weird (2 As and a null in both tables). I added a second column (ID) but didn't use it in the queries. This was just so the rows would be unique. In the rows below, * represents a null.

select * from left_t;
 
V         ID
- ----------
A          1
*          2
A          3
 
select * from right_t;
 
V         ID
- ----------
A          1
A          2
*          3
 
INNER:
 
select l.value, r.value from left_t l 
inner join right_t r on ( l.value = r.value ) ;
 
V V
- -
A A
A A
A A
A A   4 rows
 
FULL OUTER:
 
select l.value, r.value from left_t l 
full outer join right_t r on ( l.value = r.value ) ;
 
V V
- -
A A
A A
A A
A A
* *
* *   6 rows
 
LEFT OUTER:
 
select l.value, r.value from left_t l 
left outer join right_t r on ( l.value = r.value ) ;
 
V V
- -
A A
A A
A A
A A
* *   5 rows
 
RIGHT OUTER:
 
select l.value, r.value from left_t l 
right outer join right_t r on ( l.value = r.value ) ;
 
V V
- -
A A
A A
A A
A A
* *   5 rows

This makes sense from what I read about the ISO 99 for Oracle 9i. There are couple of suspicious things about the sample data:

There are no rows without a matching row on the other side, unless you count the nulls which will never match.

There are null values in the tables themselves. This is possible, but I don't think it's very common given normal usage (PK to FK) -- nulls on one side, but not both.

Duplicate values on both sides.

Only one unique (again ignoring null) value on both sides.

I suspected the sample data was the cartisian product of two tables both containing A and null, but then there should be two A/A rows. Then I figured that A simply represented a non-null value, possibly like this

1  1
*  2
3  *

but then at most 3 rows would ever be returned since the full product had 3.

So I gave up on that and replaced the sample data with this:

LEFT  RIGHT
----  -----
 A     A
 C     B

Each table has two rows ( A, B ) and ( A, B ) and no nulls -- this is not the cartisian product but rather two tables sitting next to each other. Only one row in left_t matches one row in right_t. Here are the results I got running the example same queries:

INNER:
 
V V
- -
A A   1 row
 
FULL OUTER:
 
V V
- -
A A
C *
* B   3 rows
 
LEFT OUTER:
 
V V
- -
A A
C *   2 rows
 
RIGHT OUTER:
 
V V
- -
A A
  B   2 rows

This matches the page I linked and my intuition. Did I just misunderstand the original sample data?

Thanks!
[ January 31, 2005: Message edited by: David Harkness ]

[Michael Matola]

If I understand the above data correctly, I think it's a confusing example to introduce joins for data that contains duplicates and nulls. For starters, try this instead:

table1
------
1
2
3
4
5
6

table2
------
4
6
8
10

Inner join:

select t1.* , t2.*
from table1 t1 inner join table2 t2
on t1.field = t2.field

gives you matching rows on both tables:

t1.field  t2.field
--------  --------
4         4
6         6

Left join:

select t1.* , t2.*
from table1 t1 left join table2 t2
on t1.field = t2.field

gives you matching rows on both tables and also preserves all rows on the left table by filling the results will nulls for the "missing" rows on the right table.

t1.field  t2.field
--------  --------
1
2
3
4         4
5
6         6

Right join:

select t1.* , t2.*
from table1 t1 right join table2 t2
on t1.field = t2.field

gives you matching rows on both tables and also preserves all rows on the right table by filling the results with nulls for the "missing" rows on the left table.

t1.field  t2.field
--------  --------
4         4
6  6
          8
          10

Full join:

gives you matching rows on both tables and also preserves all rows on both tables by filling the results with nulls for "missing" rows on either table.

t1.field  t2.field
--------  --------
1
2
3
4         4
5
6         6
          8
          10

[ January 31, 2005: Message edited by: Michael Matola ]

[Eddie Long]

Quite a lot of explanation there. Thanks for the help everyone.

[Jeanne Boyarsky]

Originally posted by Michael Matola:
If I understand the above data correctly, I think it's a confusing example to introduce joins for data that contains duplicates and nulls.

I agree completely. Sorry about that!