Author here. This doesn't give the correct results. It produces meal_items that have both customer_id and employee_id. Here's an excerpt (the full result set is thousands of rows, as opposed to the expected 45):

    id |label       |price|employee_id|customer_id|
    ---|------------|-----|-----------|-----------|
    ...
    344|Poke        | 4.18|       3772|      13204|
    344|Poke        | 4.18|       3313|      13204|
    344|Poke        | 4.18|       2320|      13204|
    344|Poke        | 4.18|        632|      13204|
    344|Poke        | 4.18|       4264|      13204|
    344|Poke        | 4.18|        699|      13204|
    344|Poke        | 4.18|       1070|      13204|
    344|Poke        | 4.18|       3022|      13204|
    344|Poke        | 4.18|       1501|      13204|
    344|Poke        | 4.18|        808|      13204|
    344|Poke        | 4.18|       2793|      13204|
    344|Poke        | 4.18|       1660|      13204|
    344|Poke        | 4.18|        932|      13204|
    ...

To be clear, there are ways to write this query without UNION that have both good performance and give the correct results, but they're very fiddly and harder to reason about that just writing the two comparatively simple queries and then mashing the results together.

> Author here. This doesn't give the correct results.

Right you are! :) Should have given it more thought before posting. Thanks for jumping in.

this is probably how I'd write it (assuming pg properly pushes the predicates down to the subquery in the lateral join)

  select mi.*, x.employee_id, x.customer_id
  from meal_items mi
  join lateral (
    select e.store_id, em.created, em.employee_id, customer_id = null
    from employee_markouts em
    join employees e on e.id = em.employee_id
    where em.meal_item_id = mi.id
    union all
    select c.store_id, co.created, employee_id = null, co.customer_id
    from customer_order_items coi
    join customer_orders co on co.id = coi.customer_order_id
    join customers c on c.id = co.customer_id
    where coi.meal_item_id = mi.id) x
  where x.store_id = 250 and x.created between '2021-02-03' and '2021-02-03'
  order by mi.id

OR really messes things up. UNION instead makes things fast.

Oh, I wouldn't presume that it will be faster than the union but I wonder how it compares to the original query in the article (the one with OR).