SQL - Selecting a value from conditional joins without UNION-CodePudding

Setup

create table things ( `t1_id` int, `t2_id` int);
insert into things (`t1_id`, `t2_id`)
values ( NULL, 1), (NULL, 2), (1, NULL),(2,NULL);

create table type_1 (`id` int, `name` TEXT);
insert into type_1 (`id`, `name`)
values (1, 'name 1'), (2, 'name2');

create table type_2 (`id` int, `name` TEXT);
insert into type_2 (`id`, `name`)
values (1, 'name 3'), (2, 'name4');

Query

select coalesce (t1.name, t2.name) as name
from things
join type_1 as t1 on things.t1_id IS NOT NULL AND things.t1_id = t1.id
join type_2 as t2 on things.t2_id IS NOT NULL AND things.t2_id = t2.id
group by name

Fiddle: http://sqlfiddle.com/#!9/cfce11/7

I have what I've set out in the fiddle working with UNION, but in reality the rest of the query is expensive and the union doubled this - I'll probably need to do this for a second column too (name2 from type_3 and type_4 could be in this example, things would always have either t1_id or t2_id and either t3_id or t4_id - I'm assuming the same solution would work).

I'm looking for name to be selected from one table or another depending on whether the relevant column in 'things' is null.. I could use a 'type' column in things to differentiate if it would be better.

The fiddle should return all 4 names.

CodePudding user response：

You are inner joining t1 and t2, but a thing cannot match t1 and t2 at the same time, so you get no results.

Join query

select distinct coalesce (t1.name, t2.name) as name
from things
left join type_1 as t1 on things.t1_id = t1.id
left join type_2 as t2 on things.t2_id = t2.id;

Union query

select name from type_1 where id in (select t1_id from things)
union
select name from type_2 where id in (select t2_id from things);

Both queries do the same thing. They give you all distinct names that are referenced in the things table.