Full Outer Join not working in MySql

Question

I would like to know the alternative for Full Outer Join in MySQL. I know it can be done through union but it's not working because my requirement is little complex I hope so. I have two tables master(branch_id,purchase_mindate,purchase_billvalue) and transfer(branch_id,tra_gtr_date,tra_travalue) in which both id fields will be almost same. Now I need to get the values id,sum(billvalue),sum(travalue) by joining two tables with the conditions

Mindate=current date
Gtrdate=current date
If master's table doesn't have any value but transfer table contains then transfer's value must be included
If master's table contains value but transfer doesn't then master's value must be displayed.

I tried union, join, etc, but nothing helped I know I am doing something wrong somewhere but I can't identify that please help me with this.

I used the below query,

select purchase_master.branch_id, sum(purchase_billvalue) as billvalue, sum(tra_value) as travalue 

from purchase_master 
join purchase_transfer on purchase_master.branch_id=purchase_transfer.branch_id 
where purchase_mindate=CURDATE() and tra_gtr_date=CURDATE() 
group by branch_id

I get values only if two tables contains the current date but I don't get any values if one table has and other doesn't

I have attached Screen shots of tables.

Master Table
Transfer Table

I tried the below query but the result is empty,

select pm.branch_id,pm.purchase_billvalue, pt.tra_value from purchase_master as pm
left outer join purchase_transfer as pt on pm.branch_id=pt.branch_id
where pm.purchase_mindate=CURDATE() and pt.tra_gtr_date=CURDATE()
union
select pm.branch_id,pm.purchase_billvalue, pt.tra_value from purchase_master as pm
right outer join purchase_transfer as pt on pm.branch_id = pt.branch_id
where pm.purchase_mindate=CURDATE() and pt.tra_gtr_date=CURDATE()

I need the result as sum(billvalue) sum(travalue) group by branch_id,

If two tables contains currentdate data
If any one contains the currentdate data

What do you mean by *...both id fields will be almost same*? Are they the same or not? — peterm, Sep 08 '13 at 07:24
@Ajeesh Can you post sample data (just a few rows for each table) and desired output? — peterm, Sep 08 '13 at 07:43
@Ajeesh And what is the desired output from this sample data? — peterm, Sep 08 '13 at 07:50
Show us the query that would work if `FULL JOIN` was available. — ypercubeᵀᴹ, Sep 08 '13 at 08:31

score 3 · Accepted Answer · edited May 23 '17 at 12:07

Because both tables more than one row per branch_id, you need to first GROUP BY branch_id - separately in each table - and then emulate the FULL JOIN, either with UNION ALL and one more GROUP BY or with two subqueries, one with LEFT and one with RIGHT outer join.

The 1st (GROUP BY separately, then UNION ALL plus GROUP BY) way:

SELECT branch_id, 
       SUM(purchase_billvalue) AS purchase_billvalue, 
       SUM(tra_value) AS tra_value
FROM
  ( SELECT branch_id, 
           SUM(purchase_billvalue) AS purchase_billvalue,
           NULL AS tra_value
    FROM purchase_master
    WHERE purchase_mindate = CURDATE()
    GROUP BY branch_id
  UNION ALL
    SELECT branch_id, 
           NULL, 
           SUM(tra_value)
    FROM purchase_transfer  
    WHERE tra_gtr_date = CURDATE()
    GROUP BY branch_id
) AS u
GROUP BY branch_id ;

The 3rd way to do this - which may be the most efficient method (but do test!) is provided in @peterm's answer. First do a UNION (not UNION ALL) to find all distinct branch_id from both tables and then do 2 LEFT joins to derived tables where there has been done a GROUP BY branch_id separately.

peterm · Answer 2 · 2013-09-08T08:59:13.073

2

You can do something like this

SELECT i.branch_id, 
       m.purchase_billvalue billvalue, 
       t.tra_travalue       travalue
  FROM
(
    SELECT branch_id
      FROM master
     WHERE purchase_mindate = CURDATE()
     UNION
    SELECT branch_id
      FROM transfer  
     WHERE tra_gtr_date = CURDATE()
) i LEFT JOIN 
(
  SELECT branch_id, SUM(purchase_billvalue) purchase_billvalue
    FROM master 
   WHERE purchase_mindate = CURDATE()
   GROUP BY branch_id
) m
    ON i.branch_id = m.branch_id LEFT JOIN 
(
  SELECT branch_id, SUM(tra_travalue) tra_travalue
    FROM transfer  
   WHERE tra_gtr_date = CURDATE()
   GROUP BY branch_id
) t
    ON i.branch_id = t.branch_id

Sample output (based on your screenshots and assuming that date values in both tables for all shown rows are equal):

| BRANCH_ID | BILLVALUE | TRAVALUE |
|-----------|-----------|----------|
|         2 |  72580.61 |   119947 |
|         3 |    (null) |     9940 |

Here is SQLFiddle demo

edited Sep 08 '13 at 08:59

answered Sep 08 '13 at 07:28

peterm

91,357
15
148
157

I dont think this satisfies requirements 3 and 4. Ids would have to exist in both tables for this to work. – darudude Sep 08 '13 at 07:36
@peterm I am getting error like Unknown column 't.tra_travalue' in 'field list' while trying your coding – Ajeesh Sep 08 '13 at 08:10
It means that there is a **typo** in your code (e.g. you gave a different (other than `t`) alias for `transfer` table but didn't change it in select clause ). Did you look at the working sqlfiddle example that I provided? – peterm Sep 08 '13 at 08:14
@peterm Please have a look at this fiddle [SQL Fiddle](http://sqlfiddle.com/#!2/58b47b/1), the result was not expected right? there shouldn't be any travalue right? – Ajeesh Sep 08 '13 at 08:20
@Ajeesh Right. I've updated answer and sqlfiddle. Here is [sqlfiddle](http://sqlfiddle.com/#!2/58b47b/4) with your sample data and updated query. Does it work as expected now? – peterm Sep 08 '13 at 08:27
+1 now (and you can remove the last `GROUP BY i.branch_id`): [SQL-Fiddle-2](http://sqlfiddle.com/#!2/6f47c/9) – ypercubeᵀᴹ Sep 08 '13 at 09:00
@ypercube Fair enough. Completely agreed. Thanks a lot for input. – peterm Sep 08 '13 at 09:00

Full Outer Join not working in MySql

2 Answers2