How SQL query are evaluated

by Noobie   Last Updated August 14, 2019 07:06 AM

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.



Related Questions


Updated April 09, 2018 20:06 PM

Updated June 06, 2017 18:06 PM

Updated May 15, 2017 19:06 PM

Updated March 15, 2017 02:06 AM