tsql - T-SQL: Performance when using an inner query on a join -

here's situation i'm dealing with. original query had was:

select c.custid, a.city, a.country customers c     left outer join addresses on c.custid = a.custid     c.lastname = 'jones' 

so i'd show customers last name jones, ones without address entries, , show associated addresses them. if want clause on addresses still show customers? example if this:

select c.custid, a.city, a.country customers c     left outer join addresses on c.custid = a.custid     c.lastname = 'jones' , a.country = 'united states' 

i lose customers not in united states. that's not want. want all customers last name 'jones', , omit addresses not in united states. solution came with:

select  c.custid, a.city, a.country customers c     left outer join         (select city, country addresses         country = 'united states')     on c.custid = a.custid     c.lastname = 'jones' 

in case, still customers last name jones, don't see addresses outside us, wanted.

here's issue: in third query, i'm assuming sql server fetches all addresses , join customers table, meaning many non-jones addresses fetched unnecessarily. in second query, i'm wondering if sql server fetches addresses lastname = 'jones' in first place, think make query far faster. there performance increase 2nd query on 3rd? also, whatever answer give, if comment on differences when dealing inner joins instead (if there any) that'd great.

thanks!

you can add additional constraints left join (the word outer here redundant, left , right joins automatically outer, , unqualified joins automatically inner):

select      c.custid,      a.city,      a.country   customers c  left join addresses on c.custid = a.custid , a.country = 'united states'  c.lastname = 'jones' 

as question regarding performance, real answer run both queries , compare execution plans. being said, purely join-based syntax substantially simpler, , result in simpler (and faster) execution plan.