Return default value for non-existing rows

Question

To the very basic query

SELECT id, column1, column2
FROM table1
WHERE id IN ("id1", "id2", "id3")

in which the the arguments in the where statement are passed as a variable, I need to return values also for rows where the id doesn't exist. In general, this is a very similar problem as outlined here: SQL: How to return an non-existing row? However, multiple parameters are in the WHERE statement

The result right now when id2 doesn't exist:

-------------------------------
| id  | column1   | column2   |
-------------------------------
| id1 | some text | some text |
| id3 | some text | some text |
-------------------------------

Desired outcome when id2 doesn't exist

-----------------------------------
| id  | column1     | column2     |
-----------------------------------
| id1 | some text   | some text   |
| id2 | placeholder | placeholder |
| id3 | some text   | some text   |
-----------------------------------

My first thought was to create a temporary table and join it against the query. Unfortunately, I don't have the rights to create any kind of temporary table so that I am limited to a SELECT statement.

Is there way to do that in with a SQL SELECT query?

Edit: Indeed, the above mentioned is a hypothetical situation. In the WHERE clause can be hundreds of ids where the amount of missing in unknown.

Answer 1

You can do a derived table to create something like a temp table, but it can only be used for this one query:

SELECT t.id, COALESCE(t.column1, _dflt.column1) AS column1
FROM (
  SELECT 'id1' AS id, 'placeholder text 1' as column1
  UNION ALL
  SELECT 'id2', 'placeholder text 3'
  UNION ALL
  SELECT 'id3', 'placeholder text 3'
) AS _dflt
LEFT OUTER JOIN table1 t USING (id);

---

Re comments:

I just tested the method above on MySQL 5.6.15 to see how many distinct SELECTs I can get with a series of UNION ALLs, one row per SELECT.

I got the derived table to return 5332 rows, but I think I could go higher if I had more RAM.

If I try one more UNION ALL, I get: ERROR 1064 (42000): memory exhausted near '' at line 10665. I only have 2.0GB of RAM configured on this VM.

It doesn't matter how many ids are unknown for this solution to work. Just put them all in the derived table. By using LEFT OUTER JOIN, it automatically finds those that exist in your table1, and for the ones that are missing, the entry from the derived table will be matched up with NULLs.

The COALESCE() function returns its first non-null argument, so it'll use columns from the matched rows if those are present. Where none is found, it'll default to the columns in the derived table.