I recently ran into an issue where I needed to write an IN filter that consumed values from a sub query and hard-coded list. While I could express this relatively elegantly using an ORM to run the subquery and append the results to an array of hardcoded strings, I was struggling with how to do so raw SQL query.

My first attempt was to use WITH and a simple CTE:

WITH hardcoded_user_ids(id) as (
  VALUES (1), (2)
)

SELECT * FROM orders
WHERE orders.user_id IN (
  SELECT id from normal_users
  UNION ALL
  SELECT id FROM hardcoded_user_ids
);

This was fine, but the CTE felt a bit verbose and unnecessary for my purposes. After some experimentation, I realized that this is possible using AS and specifying the column name:

SELECT * FROM orders
WHERE orders.user_id IN (
  SELECT id FROM normal_users
  UNION ALL
  SELECT id from (
    values (1) (2)
  ) as hardcoded_user_ids(id)
)

While the CTE route may actually be clearer in the long run, I like the formatting of the inline values list for simple one offs.