How to give precedence to rows from one table over matching rows in another?

I've got (key, value) pairs stored in two different tables - company_settings and branch_settings. A key can exist in none, one or both of the tables.

In case the key exists in both tables, I'd like to use the value stored in the branch_settings table. Otherwise, if available, I'd like to fall back to the value stored in the company_settings table.

company_settings
-------------------------
| key    | value
-------------------------
| key.A  | 4             |
-------------------------
| key.B  | 5             |
-------------------------

branch_settings
-------------------------
| key    | value
-------------------------
| key.A  | 1             |
-------------------------

So, if I query for key.A the result should be 1 since key.A exists in both tables and I want to give higher precedence to records from branch_settings table.

If I query for key.B the result should be 5 since key.B exists only in company_settings table.

If you need the value for only one "key", you can use COALESCE() function with 2 subqueries:

-- query 1a
SELECT COALESCE(
           (SELECT value FROM branch_settings WHERE key = prm.key),
           (SELECT value FROM company_settings WHERE key = prm.key)
       ) AS value 
FROM
    (VALUES ('key.A')) AS prm (key) ;

You could also expand the list of keys inside the VALUES, to search more than one key:

-- query 1b
FROM
    (VALUES ('key.A'), ('key.B'), ..., ('key.Z')) AS prm (key) ;

If you want to search many keys however, it might be more efficient to convert the subqueries to joins. Either two LEFT joins:

-- query 2
SELECT prm.key, COALESCE(b.value, c.value) AS value 
FROM
    (VALUES ('key.A'), ('key.B'), ..., ('key.Z')) AS prm (key)
  LEFT JOIN branch_settings AS b ON b.key = prm.key
  LEFT JOIN company_settings AS c ON c.key = prm.key ;

or a FULL JOIN, similar to @James' answer:

-- query 3
SELECT key, COALESCE(b.value, c.value) AS value 
FROM
    (SELECT * FROM branch_settings 
     WHERE key IN ('key.A', 'key.B', ..., 'key.Z')
    ) AS b
  FULL JOIN 
    (SELECT * FROM company_settings 
     WHERE key IN ('key.A', 'key.B', ..., 'key.Z')
    ) AS b
  USING (key) ;

There is a subtle difference between queries 1b, 2 and query 3. The last query will give you in the results only the (parameter) keys that appear in at least one of the tables. Query 1b and 2 will have all the (parameter) keys in the results, with NULL in value for keys that can'e be found in either table.

StackExchange Database Administrators Q#193660, answer score: 4