MySQL – Join tables to a custom list/array of values

I would like to know if it’s possible to join tables into a custom set of values in MySQL. For example, I have a set of dates (outside the database). I would like to get the matched joins in a single query so that I can easily loop through the result. Like:

Data outside database

Date
2021-04-08
2021-04-09
2021-04-10
2021-04-11
2021-04-12

The Database may look like

Table: transactions

id	date	amount
1	2021-04-08	500
2	2021-04-08	600
3	2021-04-10	350

I want the result to be like:

Date	Sum
2021-04-08	1100
2021-04-09	0
2021-04-10	350
2021-04-11	0
2021-04-12	0

In other words, I want to do:

SELECT transactions.date, SUM(amount) FROM ["2021-04-08", "2021-04-09", "2021-04-10", ...] INNER JOIN transactions ON current_value=transactions.date GROUP BY transactions.date

Answer

Example:

CREATE TABLE transactions (id INT, `date` DATE, amount INT);
INSERT INTO transactions VALUES 
(1,   '2021-04-08',   500),
(2,   '2021-04-08',   600),
(3,   '2021-04-10',   350);
SELECT * FROM transactions;

id | date       | amount
-: | :--------- | -----:
 1 | 2021-04-08 |    500
 2 | 2021-04-08 |    600
 3 | 2021-04-10 |    350

SET @outside := '2021-04-08,2021-04-09,2021-04-10,2021-04-11,2021-04-12';

WITH RECURSIVE
cte AS ( SELECT SUBSTRING_INDEX(@outside, ',', 1) `date`,
                TRIM(LEADING ',' FROM TRIM(LEADING SUBSTRING_INDEX(@outside, ',', 1) FROM @outside)) slack
         UNION ALL
         SELECT SUBSTRING_INDEX(slack, ',', 1),
                TRIM(LEADING ',' FROM TRIM(LEADING SUBSTRING_INDEX(slack, ',', 1) FROM slack))
         FROM cte
         WHERE slack != '' )
SELECT `date`, COALESCE(SUM(transactions.amount), 0) `sum`
FROM cte
LEFT JOIN transactions USING (`date`)
GROUP BY `date`
ORDER BY `date`

date       |  sum
:--------- | ---:
2021-04-08 | 1100
2021-04-09 |    0
2021-04-10 |  350
2021-04-11 |    0
2021-04-12 |    0

SELECT `date`, COALESCE(SUM(transactions.amount), 0) `sum`
FROM JSON_TABLE(CONCAT('["', REPLACE(@outside, ',', '","'), '"]'),
                "$[*]" COLUMNS (`date` DATE path "$")) jsontable
LEFT JOIN transactions USING (`date`)
GROUP BY `date`
ORDER BY `date`

date       |  sum
:--------- | ---:
2021-04-08 | 1100
2021-04-09 |    0
2021-04-10 |  350
2021-04-11 |    0
2021-04-12 |    0

db<>fiddle here