Select from query_X and then using cached_query_X in subselect

Hello.

I have a query that looks something like:

SELECT ID from query_1
WHERE ID IN (SELECT ID FROM query_2 
WHERE some_condition)
OR ID NOT IN (SELECT ID FROM cached_query_2)

query_2 takes a while to execute and I would like to use the cached result of the previous call (from the first subquery).

What result is used when I use the cached_query in the last row? Is it the result from the time the query was executed by the first subquery, or some other older result? Thank you.

Interesting question that I don’t know offhand. If you add CTE’s can test the behavior yourself. If you add a NOW() or GET_DATE() (or whichever function your database uses for current timestamp) to query_2 you can pull that as well and see which result was used.

WITH

realtime_version AS (
  SELECT id FROM query_2 WHERE some_condition
),

cached_version AS (
  SELECT id FROM cached_query_2
)

SELECT id from query_1
WHERE id in (SELECT id from realtime_version)
OR id NOT IN ( SELECT id FROM cached_version)