Select a row from one table, if it doesn't exist, select from another table

Question

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)
;

Answer 1

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)

Answer 2

Andrew Sayer's answer should be sufficient if your first table always takes priority and you only have two tables to check, and is perfectly simple. Here's a different way, with a few more steps, you can go about it that would support more than two tables and allow you to re-prioritize which order you want results from those tables:

WITH CTE_AllTables AS
(
 SELECT person, favourite_number, 1 AS table_priority
 FROM table_1 t1
 UNION ALL
 SELECT person, favourite_number, 2 AS table_priority
 FROM table_2 t2
),
CTE_AllTables_Sorted AS
(
 SELECT person, favourite_number, ROW_NUMBER() OVER (PARTITION BY person ORDER BY table_priority) AS sort_id -- Generates a unique ID per person sorted by table_priority
 FROM CTE_AllTables
)
SELECT person, favourite_number
FROM CTE_AllTables_Sorted
WHERE sort_id = 1 -- Filters out anything but the first occurrence of a person by table priority

Note you'd want to PARTITION on a key field like person_id instead of person since you can have two people with the same name even within the same table potentially, but I only had your example to go off of.

And here's how the above query can be used for an example with multiple tables and different priorities:

WITH CTE_AllTables AS
(
 SELECT person, favourite_number, 1 AS table_priority
 FROM table_1 t1
 UNION ALL
 SELECT person, favourite_number, 2 AS table_priority
 FROM table_2 t2
 UNION ALL
 SELECT person, favourite_number, 4 AS table_priority
 FROM table_3 t3
 UNION ALL
 SELECT person, favourite_number, 3 AS table_priority
 FROM table_4 t4
),
CTE_AllTables_Sorted AS
(
 SELECT person, favourite_number, ROW_NUMBER() OVER (PARTITION BY person ORDER BY table_priority) AS sort_id -- Generates a unique ID per person sorted by table_priority
 FROM CTE_AllTables
)
SELECT person, favourite_number
FROM CTE_AllTables_Sorted
WHERE sort_id = 1 -- Filters out anything but the first occurrence of a person by table priority

Four tables, no multiple WHERE predicates needed, and notice how I prioritized table_4 over table_3 by the table_priority value I gave them. You can even include the table_priority field in the final SELECT to see which table it came from. :)

Answer 3

I assume that the values of the first column of the tables are unique and not null.

Use an outer join.

select 
 coalesce(t1.person, t2.person),
 case when t1.person is null then t2.favourite_number
 else t1.favourite_number
from table_1 t1 full outer join table_2 t2 
 on (t1.person=t2.person)

• miracle173: initially this was for Oracle SQL, I now transformed it to postgresql. But this is my first postgresql statement I ever wrote and I cannot test it.