What is the correct MySql FULL JOIN syntax?

Question

Following are my two tables; I want the results as illustrated in the third table. How can I do this in MySQL (assuming FULL JOIN)?

table_sales

product_id    quantity    date
c001          20        2013-09-03
t008          30        2013-09-01
t008          20        2013-09-03
c001          90        2013-09-09

table_returns

product_id    quantity    date
t008          40        2013-09-08
t008          30        2013-09-01
c001          10        2013-09-03

I want to get result like:

product_id     sale_qty      return_qty        date
c001           20            10              2013-09-03
c001           90            -               2013-09-09
t008           30            30              2013-09-01
t008           20            -               2013-09-01
t008           -             40              2013-09-08

Yep, that’s a FULL JOIN — are you asking what the syntax is? Or how to alias things? Or…? — Ry-, Sep 08 '13 at 14:06
This has been asked before, see http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql — Alexander, Sep 08 '13 at 14:11
@Alexander I can't found answer what i want so I give this post — Md. Sahadat Hossain, Sep 08 '13 at 14:15

Gordon Linoff · Accepted Answer · 2013-09-08T14:38:28.777

My preferred method for doing a full join is to get the list of all ids and the left join to that list:

select driver.product_id, s.quantity as sale_qty, r.quantity as return_qty, driver.date
from (select product_id, date from table_sales
      union 
      select product_id, date from table_returns
     ) driver left join
     table_sales s
     on driver.product_id = s.product_id and driver.date = s.date left join
     table_returns r
     on driver.product_id = r.product_id and driver.date = r.date;

I find that putting the union in the from clause makes the query more convenient. Logic about handling the variables, formulas, and joins only have to be included once.

Here is the SQL Fiddle that demonstrates that it works.

score 4 · Answer 2 · answered Sep 08 '13 at 14:10

MySQL lacks support for FULL OUTER JOIN.

So if you want to emulate a Full join on MySQL take a look here .

This is a very helpfull link, describing the workaround for full join

sample workaround will be like

SELECT  t_13.value AS val13, t_17.value AS val17
FROM    t_13
LEFT JOIN
t_17
ON      t_13.value = t_17.value
UNION ALL
SELECT  t_13.value AS val13, t_17.value AS val17
FROM    t_13
RIGHT JOIN
t_17
ON      t_13.value = t_17.value
WHERE   t_13.value IS NULL
ORDER BY
COALESCE(val13, val17)
LIMIT 30

What is the correct MySql FULL JOIN syntax?

2 Answers2