Search code examples
sqliteselectwhere-clausewhere-in

Wanting Sqlite SELECT statement to return default values if not existing for keys in a list

I have the following table with a sample entry in SQLITE:

CREATE TABLE image (key CHAR(128) NOT NULL PRIMARY KEY, value CHAR(32) NOT NULL); INSERT INTO image VALUES ('key1', 'value1');

I would like to be able to do a lookup for a list of keys ['key1','key2'] in single SQL statement. The expected response I would like is as follows:

| key        | value         |
|************|***************|
| key1       | value1        |
| key2       | default       |

I've looked around but am unable to figure this out. I've tried the following

SELECT key, IFNULL(value, 'default') value FROM image WHERE key IN ('key1', 'key2');

and other variants involving coalesce, etc but this only returns values for key1 and not for key2 (which I know is the expected behaviour).

How can I write a single SELECT statement that gives me the above expected response? I can't seem to figure this out for the life of me. Thanks in advance.

Solution

  • A query can return only rows that actually exist in some table/view/subquery in the FROM clause; nothing you do in the SELECT expression list can change that.

    So you have to create some rows with these values (with a subquery or common table expression), and then check whether there are matching rows in the table (with an outer join):

    WITH list(key) AS (
  VALUES ('key1'), ('key2')
)
SELECT key,
       IFNULL(value, 'default') AS value
FROM list
LEFT JOIN image USING (key);