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

Question

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.

Answer 1

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.

Answer 2

This should work in any RDBMS (only with the assumption that branch keys appear in company table, with is not correct, see comments and below):

SELECT cs.key AS key, 
       CASE WHEN bs.key IS NULL THEN cs.value ELSE bs.value END AS value
FROM company_settings AS cs
LEFT OUTER JOIN branch_settings AS bs ON (cs.key=bs.key)
WHERE cs.key='whatever'

Also, if you are still on design stage, have a look at PostgreSQL inheritance here: https://www.postgresql.org/docs/10/static/ddl-inherit.html . Be wary of the various drawbacks detailed, but otherwise your use case could be a good fit to it.

EDIT taking into account that a branch key may not appear at all in the company settings key, this would be better suited:

SELECT key, value FROM
(
    SELECT 1 AS p, key, value FROM branch_settings WHERE key='whatever'
    UNION ALL
    SELECT 2 AS p, key, value FROM company_settings WHERE key='whatever'
) AS merge
ORDER BY p ASC
LIMIT 1

or

SELECT CASE WHEN cs.key IS NULL THEN bs.key ELSE cs.key END AS key,
       CASE WHEN bs.key IS NULL THEN cs.value ELSE bs.value END AS value
FROM company_settings AS cs
FULL OUTER JOIN branch_settings AS bs ON (cs.key=bs.key)
WHERE bs.key='whatever' OR cs.key='whatever';

And here is an example of using inheritance in this case (may probably not be the smarter way, it works better in the first assumption above)

test=# CREATE TABLE company_settings (key text, value text, pos int DEFAULT 1);
CREATE TABLE
test=# CREATE TABLE branch_settings (pos int DEFAULT 2) INHERITS (company_settings);
NOTICE:  merging column "pos" with inherited definition
CREATE TABLE
test=# INSERT INTO company_settings VALUES ('A', 4);
INSERT 0 1
test=# INSERT INTO company_settings VALUES ('B', 5);
INSERT 0 1
test=# INSERT INTO branch_settings VALUES ('C', 6);
INSERT 0 1
test=# INSERT INTO branch_settings VALUES ('A', 1);
INSERT 0 1
test=# SELECT key, value FROM company_settings WHERE key='A' ORDER BY pos DESC LIMIT 1;
 key | value
-----+-------
 A   | 1
(1 row)

test=# SELECT key, value FROM company_settings WHERE key='B' ORDER BY pos DESC LIMIT 1;
 key | value
-----+-------
 B   | 5
(1 row)

test=# SELECT key, value FROM company_settings WHERE key='C' ORDER BY pos DESC LIMIT 1;
 key | value
-----+-------
 C   | 6
(1 row)

test=# SELECT key, value FROM company_settings WHERE key='D' ORDER BY pos DESC LIMIT 1;
 key | value
-----+-------
(0 rows)

Answer 3

For a single key value

So, if I query for key.A ...

Sounds like you typically query for a single key value. The simplest and fastest way to do this has not been posted, yet:

             SELECT value FROM branch_settings  WHERE key = 'key.A'
   UNION ALL SELECT value FROM company_settings WHERE key = 'key.A'
-- UNION ALL SELECT 0  -- optional default (with matching type!)
   LIMIT 1;

Postgres will stop executing as soon as the first row is found. Testing with EXPLAIN ANALYZE, you'll see (never executed) for the second query if the first query returns a row.

The third SELECT is totally optional, but a cheap and elegant way to default to value if the key is in neither of the two tables.

Note a subtle difference to using COALESCE((SELECT ..), (SELECT ..)): My query returns whatever is found in the column if the row exists - including potential NULL values, which can happen if the column value isn't defined NOT NULL. COALESCE, on the other hand, cannot distinguish between two distinct cases of NULL:

1. The search for 'key.A' returned no row.
2. The search for 'key.A' returned a row but the value is NULL.

You may or may not want to lump both cases together. But be aware of the difference.

Related:

• How to force order of WHERE clauses in PostgreSQL?

For multiple key values

Nested in a LATERAL subquery, the same technique works for any number of values:

SELECT k.key, v.value
FROM  (VALUES ('foo'::text), ('bar'), ('baz')) k(key)  -- could be a table or query too
LEFT   JOIN LATERAL (
             SELECT value FROM branch_settings  WHERE key = k.key
   UNION ALL SELECT value FROM company_settings WHERE key = k.key
   -- UNION ALL SELECT 0
   LIMIT 1
   ) v ON true;

In a cursory test, this was several times faster than LEFT JOIN + COALESCE for 500 keys. It reduces work for Postgres to a minimum.

LEFT JOIN LATERAL (...) ON true retains key values without match (which can only happen without the commented last SELECT). To drop such rows instead:

SELECT k.key, v.value
FROM   k, LATERAL (
             SELECT value FROM branch_settings  WHERE key = k.key
   UNION ALL SELECT value FROM company_settings WHERE key = k.key
   LIMIT 1
   ) v;

You still get NULL in value for actual NULL values in branch_settings.value or company_settings.value. Adapt the query to handle distinct cases any way you need.

• What is the difference between LATERAL and a subquery in PostgreSQL?
• Why is array_agg() slower than the non-aggregate ARRAY() constructor?
• Understanding multiple table join with aggregation

Answer 4

In Sql Server (probably, but not always the same as postgresql - for example, I had to use key_' and not 'key'), I'd try this:

select isnull(b.key_,c.key_) as key_, isnull(b.value, c.value) as value
from branch_settings b
full outer join company_settings c on b.key_ = c.key_ ;

I tested it like so:

create table company_settings (key_ varchar(255), value int) ;
create table branch_settings (key_ varchar(255), value int) ;

insert into company_settings (key_, value)
values
('Key.A',4),
('Key.B',5) ;

Insert into branch_settings (key_, value)
values ('Key.A',1) ;

select isnull(b.key_,c.key_) as key_, isnull(b.value, c.value) as value
from branch_settings b
full outer join company_settings c on b.key_ = c.key_ ;

And my results were

key_    value
Key.A   1
Key.B   5

Answer 5

Below are three more options... take a look at this SQL Fiddle to see them in action.

1. FULL OUTER JOIN

I find this the most elegant and sane. Performance is probably the best as well, but table/index size among lots of other factors will impact performance. The join may cost you more than other options, but I may choose this anyway.

SELECT
  key,
  coalesce(branch_settings.value, company_settings.value)
FROM
  company_settings
  FULL OUTER JOIN branch_settings USING (key);

Bonus, you can add your parameters in a single WHERE clause like any other query.

WHERE
  key = 'key.A' -- single
  OR key IN ('key.A', 'key.B', ..., 'key.Z') -- several

2. DISTINCT ON ...

This option works well and has the added benefit of removing the full outer join at the expense of adding the DISTINCT ON clause... not free, but may be better given other factors.

SELECT
  DISTINCT ON (
    key
  )
  key,
  value
FROM
  (
    SELECT 1 AS priority, key, value FROM branch_settings
    UNION ALL
    SELECT 2 AS priority, key, value FROM company_settings
  ) AS "sub"
ORDER BY
  key,
  priority;

3. COMBINING QUERIES

Depending on how often you have branch_settings overriding company_setting this could also be a viable option. The biggest downside is that it hits both tables twice. That said, if the second request is relatively limited it could be a viable option.

SELECT
  key,
  value
FROM
  branch_settings
UNION ALL
SELECT
  key,
  value
FROM
  (
    SELECT key FROM company_settings
    EXCEPT
    SELECT key FROM branch_settings
  ) AS "sub"
  JOIN company_settings USING (key);