Postgresql – Select a row from one table, if it doesn’t exist, select from another table

postgresqlpostgresql-9.6select

How can I select a row from one table, but if it doesn't exist, fall back to selecting it from a secondary table? Here's a dumbed down version of what I'm trying to achieve

create table table_1 (
  person TEXT,
  favourite_number INT
  );
  
  create table table_2 (
    person TEXT,
    favourite_number INT
  );
  
  insert into table_1 (person, favourite_number) values 
  ('Bob', 1),
  ('Fred', 2)
  ;
  
    insert into table_2 (person, favourite_number) values 
  ('Bob', 30),
  ('Alice', 70)
  ;

I want to get the following result:

| person | favourite_number |
|--------|------------------|
| Bob    | 1                |
| Alice  | 70               |
| Fred   | 2                |

Notice how it picks up Bob and Fred from the first table. Even though Bob appears in the second, because we've already got him we take him from the first table. Alice only appears in the second table.

This is what I've tried so far, but I can't quite get all 3 returning back, please help.

select
  t1.*
from table_1 t1
where t1.person not in (select person from table_2)
union
select
  t2.*
from table_2 t2
where t2.person not in (select person from table_1)
;

Best Answer

If it is preferable to select the rows from the first table, you should take out the filter that would remove them when the person exists in the other.

There’s also no need to distinct the rows, so use union all instead of union

select
  t1.*
from table_1 t1
union all
select
  t2.*
from table_2 t2
where t2.person not in (select person from table_1)
Related Question