How SQL query are evaluated I ran into an issue on trying to optimise the following two query into 1 single query.

Result 1: 

select * 
from sip_trunks 
where resporg_account_id_id=62 
and identifier = (select action 
                  from sip_trunk_numbers 
                  where number='xxxxxxxxxx' 
                  and resporg_id=80 limit 1) 
limit 1


Result 2: 

select * 
from sip_trunks 
where resporg_account_id_id=62 
and sip_trunks.default=true 
limit 1


Final Output = Result 1 + Result 2

Thus far I have managed to achieve this.

Note: I can't perform a UNION since I'm not sure whether RESULT 1 would yield any result or not (hence the flag matcher is used in below query. other than checking the rows returned) I know that Result 2 would always be there.

select *, 
        (case 
           when identifier=(select action from sip_trunk_numbers where number='xxxxxxxxxx' and resporg_id=80 limit 1) 
             then 1
           when sip_trunks.default=true 
             then 2
           else 3
         end) as matcher
from sip_trunks 
where resporg_account_id_id=62 
order by matcher asc 
limit 2;


Now I'm stuck.

a: I can't group the result based on the matcher as Postgres complain the '*' is not used in the group.

But First I'm not sure how bad is the subquery is (select action from sip_trunk_numbers where number='xxxxxxxxxx' and resporg_id=80 limit 1) 

So does using a subquery as column would make it run for all the rows or it would run for rows for which the where conditions matched.

In other word does the subquery would run for all rows of sip_trunks or it would only run for rows for which the resporg_account_id_id=62 matches.
Why don't you use an OR condition? 

select * 
from sip_trunks 
where resporg_account_id_id = 62 
  and (   sip_trunks.default = true 
       or identifier = (select action 
                        from sip_trunk_numbers 
                        where number = 'xxxxxxxxxx' 
                        and resporg_id = 80 
                        limit 1))

limit 1




If you need to know which condition matched, you can use a UNION:

select *
from (
  select st.*, 1 as source
  from sip_trunks st
  where resporg_account_id_id = 62 
    and identifier = (select action 
                      from sip_trunk_numbers 
                      where number = 'xxxxxxxxxx' 
                        and resporg_id = 80 
                      limit 1) 
  limit 1

  union all

  select st.*, 2 as source
  from sip_trunks st
  where resporg_account_id_id = 62 
    and sip_trunks.default = true 
  limit 1  
) 
order by source 
limit 1


If source equals one in the result the first query matched, otherwise the second.



Alternatively you could use an outer join:

select st.*, 
       stn.action is not null as identifier_matched
from sip_trunks st
  left join (
    select action 
    from sip_trunk_numbers 
    where number = 'xxxxxxxxxx' 
      and resporg_id = 80 
    limit 1  
  ) stn on stn.action = st.identifier
where resporg_account_id_id = 62 
  and (stn.action is not null or sip_trunks.default = true)
limit 1


Note that limit without an order by usually makes no sense.