Postgresql – Extending condition in a join with Postgres

postgresql

I have a situation like this:

with
my_data as (
  select A.a1,B.b1,B.b2
  from some_table A
  left join lateral (
     select b1, b2 from X where X.id = A.id limit 1
  ) B ON TRUE
)
select * from my_data where b1 is not null
union all
select A.a1,C.b1,C.b2
from my_data A, lateral (
   select b1, b2 from Y where Y.id = A.id limit 1
) C
where A.b1 is null;

and it returns the expected result but I would like to know if there is a better way of writing it.

For example one iteration like:

select A.a1,B.b1,B.b2
from some_data A, lateral (
   select b1, b2 from X where id = A.id limit 1
   -- and if the query on 'X' fails then try:
   select b1, b2 from Y where id = A.id limit 1
) B

any idea?

Best Answer

select b1, b2 from X where id = A.id limit 1
-- and if the query on 'X' fails then try:
select b1, b2 from Y where id = A.id limit 1

In general, you can implement this with UNION ALL, and then LIMITing the entire query so that the second query is taken only if the first one did not return something:

select b1, b2, 1 as order_me from X where id = A.id
union all
select b1, b2, 2             from Y where id = A.id
order by order_me
limit 1
Related Question