full outer join in mysql

Hua
Hua Member
in Magic ETL

I noticed that "full outer join" is not working in MySQL.  Only Left Outer Join & Right Outer Join can work.  Can someone help me achieve "full outer join" using MySQL?  Do I have to switch to RedShift for that?

 

Thanks,
Hua

Best Answer

  • Godiepi
    Godiepi Coach
    Answer ✓
    SELECT * FROM t1
    LEFT JOIN t2 ON t1.id = t2.id
    UNION ALL
    SELECT * FROM t1
    RIGHT JOIN t2 ON t1.id = t2.id
    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'

Answers

  • Godiepi
    Godiepi Coach
    Answer ✓
    SELECT * FROM t1
    LEFT JOIN t2 ON t1.id = t2.id
    UNION ALL
    SELECT * FROM t1
    RIGHT JOIN t2 ON t1.id = t2.id
    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • Hua
    Hua Member

    That is exactly what I'm using now.   Thank you.

  • byusteve
    byusteve Member

    Don't you need to add a "SELECT DISTINCT" after compiling this table to avoid duplicates wherein the records were on both tables?

  • Godiepi
    Godiepi Coach

    If the desire of the user is to avoid duplicates , instead of a "select Distinct" , a "Where" case can be added to the second query .... tha'll do the job. 

     

    this way :

      SELECT * FROM t1
      LEFT JOIN t2 ON t1.id = t2.id
      UNION ALL
      SELECT * FROM t1
      RIGHT JOIN t2 ON t1.id = t2.id
      WHERE t1.id IS NULL

     

    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'

Categories