constraining a joined table

Question

constraining a joined table

garryurban

SSC-Addicted

Points: 443
More actions
January 19, 2006 at 1:50 am

#113161

I have two table tbl_record and tbl_boundaries

they have a common field - postcode

but the constraint is placed on the tbl_boundaries table

which would be prefereable:

select t1.*,t2.authority
from [tbl_record] t1
join [tbl_boundaries] t2 on t1.postcode=t2.postcode
where t2.authotity='westminster'

or

select t1.*,t2.authority
from [tbl_record] t1
join (select postcode,authority
from tbl_boundaries where authotity='westminster') t2 on t1.postcode=t2.postcode
] t2 on t1.postcode=t2.postcode

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Vladan SSC-Insane Points: 22072 More actions · Answer 1

I don't see any reason why you should not use standard join (the first version), or how using a derived table could improve performance. Preferable because of simplicity and readability is IMHO the first version. If this is just a part of some complicated code, or a dummy example, then it could be possible that your real code could profit from or even require the use of derived table - but I don't think so, at least nothing in your post indicates that.