A LEFT JOIN can solve it, like Laurenz provided.
But I suggest an ARRAY constructor in a LATERAL subquery instead:
SELECT l.name, b.book_list
FROM library l
CROSS JOIN LATERAL (
WHERE library = l.id
WHERE l.id = :library_no;
This way, you don't need to aggregate in ...
The values for id 5 and LIMIT must be calculate beforehand and passed on to the query, because LIMIT must have fixed values or you have to use a prepared statement
CREATE TABLE table1
(`id` int, `level` int, `score` int, `timestamp` varchar(19))
INSERT INTO table1
(`id`, `level`, `score`, `timestamp`)
(4, 1, 70, '2021-01-14 21:50:...
In general you should include the scripts that generate your Table schemas and populates them with data. (Also for performance questions, you should include the execution plan too.) I couldn't do a lot of testing without real data, but I believe this query should improve the performance you're seeing:
DECLARE @my DATE = '2019-12-01'
DECLARE @nextMonthYear ...
The planner doesn't understand how the restriction on s.subscription_id interacts with the merge join. The index scan on "s" gets to quickly skip about half the tuples by jumping to the middle of the index, while the index scan on "si" then will read through all the tuples that correspond to the quickly-skipped ones from table "s&...
You need to group by the submission_id:
SELECT submission_id sub_id,
FROM form_results d
JOIN field_options c1 ON FIND_IN_SET(c1.option_value, d.datimkt)
GROUP BY sub_id;