Full Outer Join (More Than Two Tables)

Posted on 2006-03-21
Last Modified: 2010-05-18
I'm not even sure myself if this makes sense but can you join more than two tables with the new FULL outer join syntax.

For example if I have three tables can I join all three hoping to return all rows on all three tables only matching rows where the join condition is satisfied.

If this is possible, or even logical, give a code example.
Question by:Mick_Rice
    LVL 19

    Expert Comment

    "only matching rows where the join condition is satisfied." - this requires only an inner join between the tables

    taking a 2 table example, customers and invoices, inner join them and get a list of all invoices that have a valid customer (or all customers that have a valid invoice - it's the same) left/right outer join them and get either all customers with their invoices, if they have any, or all invoices with a valid customer, full outer join will list all customers and all invoices, showing where they link
    LVL 42

    Accepted Solution

    Haven't tried it, but I don't see any reason why full outer join across three tables wouldn't work. In reality, it's just two successive full outer joins:  Table1 full outer join table2 produces a result set and then full outer join that result set to table3.  

    The result of a full outer join is the same as inner joining two tables then adding the rows in each table that do not have a matching row in the other table.  Columns from the missing table are represented by nulls.  

    For example:



    AlbumID  SongID
    ---------   --------
    a1           s1
    a2           s1
    a2           s2  

    Select * from tblSong as s
    full outer join tblRelease as r on s.songID = r.SongID
    full outer join tblAlbum   as a on a.albumID = r.AlbumID

    s.songid       r.songid      r.albumid       a.albumID
    ---------       ---------       ----------        -----------
    s1               s1               a1                 a1
    s1               s1               a2                 a2
    s2               s2               a2                 a2
    s3               null             null               null
    null             null             null                a3


    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now