wood burning stoves*
The moose likes JDBC and the fly likes Inner and Outer Joins Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Inner and Outer Joins" Watch "Inner and Outer Joins" New topic
Author

Inner and Outer Joins

Eddie Long
Ranch Hand

Joined: Nov 02, 2003
Posts: 69
Please explain the difference between these two type of joins. I can't seem to understand it


...and so help me God.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30065
    
149

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:

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


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
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):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:
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

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
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
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. 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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30065
    
149

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
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
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.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 thisbut 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: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: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
    whippersnapper
    Ranch Hand

    Joined: Mar 25, 2001
    Posts: 1744
        
        2
    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:



    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.



    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.



    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.


    [ January 31, 2005: Message edited by: Michael Matola ]
    Eddie Long
    Ranch Hand

    Joined: Nov 02, 2003
    Posts: 69
    Quite a lot of explanation there. Thanks for the help everyone.
    Jeanne Boyarsky
    internet detective
    Marshal

    Joined: May 26, 2003
    Posts: 30065
        
    149

    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!
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Inner and Outer Joins
     
    Similar Threads
    Regarding Hibernate Query
    repeates each and everytime
    List
    is there a difference between JOIN and INNER JOIN in JPQL ?
    EJB-QL