1

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?

Randomize
  • 1,183
  • 1
  • 20
  • 33

1 Answers1

1
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
CL.
  • 5,213
  • 1
  • 20
  • 23
  • if that avoid unnecessary query to the second one, it is great :) – Randomize Mar 21 '19 at 09:16
  • is the ordering required? – Randomize Mar 21 '19 at 09:30
  • The obvious (and currently used) implementation of UNION ALL is to append the second set of rows to the first one, but the Postgres documentation does not guarantee that this will actually happen in future versions. – CL. Mar 21 '19 at 09:41
  • from the documentation I can read: UNION effectively appends the result of query2 to the result of query1 (although there is no guarantee that this is the order in which the rows are actually returned). So the order is needed. Just wondering if the second query gets actually executed even if not needed (I mean if the planner is so smart to catch it) – Randomize Mar 21 '19 at 09:51
  • my point is, if it runs the second one only if the first returns nothing, the ordering is not needed, otherwise is not efficient – Randomize Mar 21 '19 at 10:00
  • The planner is not smart enough to detect that the 2 will fall out of the LIMIT. If you want to be as efficient as possible, do not user ORDER BY, and be prepared for it to maybe blow up in some future version. – CL. Mar 21 '19 at 10:30
  • 1
    @CL.: In my experience, the planner is smart enough to detect that. You'll see "never executed" for the second SELECT if the first one returns any rows. See: https://dba.stackexchange.com/a/193914/3684, https://dba.stackexchange.com/a/184309/3684 – Erwin Brandstetter Mar 21 '19 at 10:45
  • @ErwinBrandstetter It isn't with ORDER BY. – CL. Mar 21 '19 at 16:02
  • @CL.: Right, not with ORDER BY after UNION ALL. That's logically impossible. – Erwin Brandstetter Mar 21 '19 at 16:16