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?

• J.D.: "These sums are correct but not in 2 separate columns 'sales' and 'budget'." - What do you mean by this?...are you trying to net the sales against the budget?
• user3069332: Yes indeed, but now to are added on new rows but I want them represented in separate columns
• Rick James: Eh? " joined the 2 tables together on date and location" -- But your desired output does not involve "date"??
• user3069332: Yes, dates to get the sum within a certain timeframe but the sum itself is the desired output

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.

• user3069332: Sorry I cannot mark as correct because of my reputation but this works. Thanks so much for the explanation!
• J.D.: @user3069332 No problem, glad it works! There's no limitation on marking answers as correct (it's just the ✔️ checkmark button), you just probably can't upvote it which is fine. But no worries either way.
• user3069332: I’ve clicked to indicate it as correct, thanks again :)

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	...	...