full outer join in mysql

Hua

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

Godiepi

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

Godiepi

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

Hua

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

byusteve

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

Godiepi

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