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?

"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? — J.D.
– J.D., Commented Feb 14, 2022 at 21:54
Yes indeed, but now to are added on new rows but I want them represented in separate columns — user3069332
– user3069332, Commented Feb 14, 2022 at 22:54
Eh? " joined the 2 tables together on date and location" -- But your desired output does not involve "date"?? — Rick James
– Rick James, Commented Feb 15, 2022 at 22:54
Yes, dates to get the sum within a certain timeframe but the sum itself is the desired output — user3069332
– user3069332, Commented Feb 16, 2022 at 9:40

J.D. · Accepted Answer · 2022-02-15 00:45:39Z

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.

Sorry I cannot mark as correct because of my reputation but this works. Thanks so much for the explanation! — user3069332
– user3069332, Commented Feb 15, 2022 at 7:59
@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. — J.D.
– J.D., Commented Feb 15, 2022 at 11:45

Andriy M · Accepted Answer · 2022-02-25 15:34:33Z

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.

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

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

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

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

Stack Exchange Network

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

2 Answers 2

Your Answer

Hot Network Questions

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

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions