Results 1 to 4 of 4

Thread: [SQL] Full Outer Join for DBMS that don't support it

  1. #1

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    [SQL] Full Outer Join for DBMS that don't support it

    Hi Folks,
    recently i stumbled upon the fact that there are (popular) Database-Systems, which don't support a Full Outer Join out of the box.
    Prominently there are SQLite and MySQL.

    I worked out a Workaround.
    Sample-Code is for SQLite
    Code:
    CREATE TABLE "tbl_left" (
        "ID"    INTEGER NOT NULL UNIQUE,
        "ValueLeft"    TEXT,
        PRIMARY KEY("ID" AUTOINCREMENT)
    );
    CREATE TABLE "tbl_right" (
        "ID"    INTEGER NOT NULL UNIQUE,
        "ValueRight"    TEXT,
        PRIMARY KEY("ID" AUTOINCREMENT)
    );
    /*Sample Data for both tables*/
    INSERT INTO tbl_right VALUES (2,'test 2'),(3,'test 3'),(4,'test 4'),(5,'test 5'),(7,'test 7'),(8,'test 8'),(10,'test 10');
    INSERT INTO tbl_left VALUES (1,'test 1'),(2,'test 2'),(4,'test 4'),(5,'test 5'),(6,'test 6'),(8,'test 8'),(9,'test 9'),(10,'test 10')
    And here's the workaround:
    The trick is: You have to make a DISTINCT UNION on both tables which you use as the master appending two LEFT JOIN's to it.
    Code:
    SELECT l2.ID As LeftID, l2.ValueLeft, r2.ID AS RightID, r2.ValueRight 
    FROM 
    (SELECT DISTINCT l.ID As ID
    FROM tbl_left As l UNION SELECT r.ID AS ID FROM tbl_right As r) As C
    LEFT JOIN tbl_left as l2 ON C.ID=l2.ID LEFT JOIN tbl_right As r2 ON r2.ID=C.ID ORDER BY c.ID
    Returns:

    LeftID ValueLeft RightID ValueRight
    1 test 1
    2 test 2 2 test 2
    3 test 3
    4 test 4 4 test 4
    5 test 5 5 test 5
    6 test 6
    7 test 7
    8 test 8 8 test 8
    9 test 9
    10 test 10 10 test 10
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: [SQL] Full Outer Join for DBMS that don't support it

    You can also do a left-right and union the results:
    Code:
    SELECT * FROM t1 
    LEFT JOIN t2 ON t1.id = t2.id 
    UNION 
    SELECT * FROM t1 
    RIGHT JOIN t2 ON t1.id = t2.id
    Also... I'm a bit surprised it hasn't been implemented in mySQL...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: [SQL] Full Outer Join for DBMS that don't support it

    Btw, UNION is always returning unique rows (so no DISTINCT is required) and is a relatively expensive operation compared to UNION ALL which is cheap because it does not deduplicate.

    OUTER JOIN can be emulated with INNER JOIN and a UNION ALL like this (in pseudo-SQL)

    a LEFT JOIN b <=> a JOIN b UNION ALL a WHERE a.ID NOT IN (b.ID)

    This idea can be used with FULL OUTER JOIN too like this

    Code:
    SELECT l.ID, l.ValueLeft, r.ID, r.ValueRight
    FROM tbl_left l
    LEFT JOIN tbl_right r
    ON l.ID = r.ID
    
    UNION ALL
    
    SELECT NULL, NULL, r.ID, r.ValueRight
    FROM tbl_right r
    WHERE r.ID NOT IN (SELECT ID FROM tbl_left)
    (not tested)

    cheers,
    </wqw>

  4. #4

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: [SQL] Full Outer Join for DBMS that don't support it

    OK, many ways to skin that cat.

    Agreed on the DISTINCT vs. UNION
    In my Queries the DISTINCT sneaks in pretty much every time i start writing a Query, so i blame it on (bad) habit.

    EDIT: After thinking about it, what i do like with my approach: I'm not bound to two tables.
    I can join as many tables as i want, as long as the Field you want to Join the tables to of each table is in the primary SELECT UNION
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width