mysqlsqljoinleft-joinfull-outer-join

Combining Left & Right Join in MySQL query


I am joining 2 tables -tbl1 and tbl2. Left join give all data from tbl1 which is in tbl2 or only on tbl1. Right join gives data from tbl2 which is don't exists in tbl1.

I want to combine both results.

What is the best way to do this so that I get all data from tbl1 and tbl2?


Solution

  • The only you can do that is by using UNION. MySQL doesn't support FULL JOINjust like in MSSQL.

    SELECT * 
    FROM tbl1 t1 
           LEFT JOIN tbl2 t2
              ON t1.col = t2.col
    UNION 
    SELECT * 
    FROM tbl1 t1 
           RIGHT JOIN tbl2 t2 
              ON t1.col>= t2.<col
    

    SEE HERE: Simulating FULL JOIN in MYSQL

    By the way, UNION has optional keyword ALL,when the ALL is omitted, UNION automatically selects DISTINCT rows from the resultset.

    EXAMLE:

    SELECT *
    FROM   tableA
    UNION ALL
    SELECT *
    FROM   tableA
    

    this can result duplicates rows

    ColA    ColB
    ==================
    1       John
    2       Jade
    2       Jade
    3       Hello
    

    BUT if you omit the word ALL

    SELECT *
    FROM   tableA
    UNION
    SELECT *
    FROM   tableA
    

    this can result distinct rows only

    ColA    ColB
    ==================
    1       John
    2       Jade
    3       Hello