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

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

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

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. :)

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)
``````
• 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. Feb 13 at 23:16