Is it possible to LEFT JOIN a @variable table in SQL

Question

Summary

I am attempting to LEFT JOIN on a filtered TABLE in SQL.

To do so I am first declaring a temporary table in an @ variable, then attempting to join on it later.

Unfortunately I am not having much luck doing this, it massively speeds up my query when limiting to such a resultset.

Other Routes Tried

I initially was trying to conduct this on the WHERE, however this was preventing rows from occurring where there were no matching events (I am counting the events by intervals_days so I need to return regardless of whether there's matching in the other).

After I realised my mistake, I moved to the ON. I have not seen examples of this done, and I have a feeling doing a FIND_IN_SET here on a SELECT would not be performant?

QUERIES

The Initial Filter

DECLARE @filtered_events TABLE (id INT, eventable_id INT, eventable_type VARCHAR(255), occurred_at TIMESTAMP, finished_at TIMESTAMP)
INSERT INTO @filtered_events
SELECT
    e.id, e.eventable_id, e.eventable_type, e.occurred_at, e.finished_at
FROM
    units_events as ue
LEFT JOIN
    events as e
ON
    e.id = ue.event_id
WHERE
    ue.unit_id
IN
    (1,2,3);

Ignore the (1,2,3) here, these values are added to the query dynamically.

My Attempted Use Of It

SELECT
    i.starts_at as starts_at,
    i.ends_at as ends_at,
    count(fe.id) as count
FROM
    intervals_days as i
LEFT JOIN
    @filtered_events as fe
ON
    ( fe.occurred_at >= starts_at AND fe.occurred_at < ends_at )
WHERE
    ( starts_at >= '15-01-2019' AND ends_at < NOW() )
GROUP BY
    starts_at
ORDER BY
    starts_at
DESC;

These queries are within one SQL document, one above the other with the terminating ; semicolon on each.

I expect this to output what my lower query outputs (a grouped resultset by the intervals_days rows)- however with the benefit of my LEFT JOIN query being conducted on a much smaller sample.

Answer 1

Sorry, no such syntax.

MySQL has no concept of arrays, either.

Nor can you use DECLARE outside of a Stored Routine.