How can I mimic full outer join in mysql with multiple columns

Question

I have two tables: A and B.

Table A has the following set-up:

ID	date	location	sales
1	2022-01-01	1	10000
2	2022-01-02	1	10000
3	2022-01-04	1	10000
...	....	2	....

So there is no data for for the location 1 at the date 2022-01-03.

Table B has the following set-up:

ID	date	location	budget
1	2022-01-01	1	10000
2	2022-01-03	1	10000
3	2022-01-04	1	10000
...	....	2	....

So there is no record for location 1 for the date 2022-01-02.

I am trying to join the tables together to get the following output

location	sales	budget
1	30000	30000
2	...	...

So I can group it on location and get | location ABC | sales 123 | budget 123 |, which is a sum of all the dates grouped, but also joined the 2 tables together on date and location.

The query I currently have now is as follows:

SELECT SUM(A.sales) AS sales, A.restaurant
FROM A
LEFT OUTER JOIN B ON A.date = B.date AND A.location= B.location
WHERE A.date between ? AND ? 
GROUP BY A.location
UNION
SELECT SUM(B.budget) AS budget, B.restaurant
FROM A
RIGHT OUTER JOIN B ON A.date = B.date AND A.restaurant = B.restaurant
WHERE B.date between ? AND ?  
GROUP BY B.restaurant

I've tried different types of joins and unions and ended up with a query as suggested in this Answer to mimic a full outer join. However, with this query I get the following output:

location	column
1	30000
2	...
3	...
1	30000
2	...
3	...

These sums are correct, but are not in 2 separate columns 'sales' and 'budget'.

Is there a way to achieve this?

Answer 1

In a UNION clause the alignment of the columns in each unioned dataset defines the order of those columns in the final result set.

Since you don't want the sales and budget data points to be in the same column, you can just un-align them and add placeholder default values in the other dataset like so:

SELECT 0 AS budget, SUM(A.sales) AS sales, A.restaurant
FROM A
LEFT OUTER JOIN B ON A.date = B.date AND A.location= B.location
WHERE A.date between ? AND ? 
GROUP BY A.location
UNION
SELECT SUM(B.budget) AS budget, 0 AS sales, B.restaurant
FROM A
RIGHT OUTER JOIN B ON A.date = B.date AND A.restaurant = B.restaurant
WHERE B.date between ? AND ?  
GROUP BY B.restaurant

Then to get the final results you want, you'll want to do one more grouping again on location in a subquery or CTE to aggregate the different sides of the UNION into a single row like so:

SELECT location, SUM(sales) AS sales, SUM(budget) AS budget
FROM 
(
    SELECT 0 AS budget, SUM(A.sales) AS sales, A.location
    FROM A
    LEFT OUTER JOIN B ON A.date = B.date AND A.location= B.location
    WHERE A.date between ? AND ? 
    GROUP BY A.location
    UNION
    SELECT SUM(B.budget) AS budget, 0 AS sales, B.restaurant
    FROM A
    RIGHT OUTER JOIN B ON A.date = B.date AND A.restaurant = B.restaurant
    WHERE B.date between ? AND ?  
    GROUP BY B.restaurant
) Results
GROUP BY location

Note I think I fixed a typo in your first dataset of the UNION clause by changing A.restaurant to A.location.

Answer 2

J.D. has explained how to arrange the different aggregates in different columns in the end result. Let me address another issue.

It seems to me that for your problem, not only can you do without a full join, emulated or real, you actually do not need any join at all. You want aggregates per location from one table, and aggregates per location from the other table. And you want the aggregates from the different tables to be on the same row if they are for the same location.

So this is how you can go about it.

1. Get the aggregates from one table:

   SELECT
     location
   , SUM(sales) AS sales
   FROM
     A
   WHERE
     date BETWEEN ? AND ?
   GROUP BY
     location
   

   You should get something like this:

   location	sales
   1	30000
   2	...

2. Get the aggregates from the other table:

   SELECT
     restaurant
   , SUM(budget) AS budget
   FROM
     B
   WHERE
     date BETWEEN ? AND ?
   GROUP BY
     restaurant
   

   Output:

   restaurant	budget
   1	30000
   2	...

3. UNION the two results, applying J.D.'s suggestion of using placeholders:

   SELECT
     location
   , SUM(sales) AS sales
   , 0          AS budget
   FROM
     A
   WHERE
     date BETWEEN ? AND ?
   GROUP BY
     location
   
   UNION ALL
   
   SELECT
     restaurant
   , 0
   , SUM(budget)
   FROM
     B
   WHERE
     date BETWEEN ? AND ?
   GROUP BY
     restaurant
   

   The output in this case should look like this:

   location	sales	budget
   1	30000	0
   2	...	...
   ...	...	...
   1	0	30000
   2	...	...
   ...	...	...

4. Now all you need is to aggregate the data once more over the combined set:

   SELECT
     location
   , SUM(sales ) AS sales
   , SUM(budget) AS budget
   FROM
     (
       SELECT
         location
       , SUM(sales) AS sales
       , 0          AS budget
       FROM
         A
       WHERE
         date BETWEEN ? AND ?
       GROUP BY
         location
   
       UNION ALL
   
       SELECT
         restaurant
       , 0
       , SUM(budget)
       FROM
         B
       WHERE
         date BETWEEN ? AND ?
       GROUP BY
         restaurant
     ) AS derived
   ;
   

   And that should give you the expected output of

   location	sales	budget
   1	30000	30000
   2	...	...