full outer join in mysql

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 🟣
    Accepted 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

Answers

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

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

  • 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