order of joins

[timc]

i have below sql with several joins. Under sql 2000 should it be assumed that sql will create the best plan for however you set the joins or is there an order when using left joins to follow

select p.account_id, p.sex, p.other_id_number,
ltrim(rtrim(upper(p.last_name))) + ', ' + ltrim(rtrim(upper(p.first_name))) as pat_name,
pe.create_timestamp as enc_date, loc.location_name, l.ngn_status, l.sign_off_person,
l.sign_off_date,
ltrim(rtrim(upper(pr.first_name))) + ' ' + ltrim(rtrim(upper(pr.last_name))) as signoff_name,
ltrim(rtrim(upper(prv.first_name))) + ' ' + ltrim(rtrim(upper(prv.last_name))) as prov_name,
r.req_accession, l.ufo_num, r.spec_rcv_date_time, r.date_time_reported,
r.test_desc, x.abnorm_flags, x.obs_id, x.result_desc, x.ref_range, x.units,
x.observ_value, c.comment_text
from patient p
inner join patient_ p1 on p1.account_id = p.account_id
inner join patient_encounter pe on pe.account_id = p.account_id
inner join location loc on loc.location_key = pe.location_id
inner join lab_nor l on l.enc_id = pe.enc_id
inner join provider prv on prv.provider_id = l.ordering_provider
left outer join profile pr on pr.user_id = l.sign_off_person
inner join p_lab_results_obr r on r.ngn_order_num = l.order_num
inner join i_lab_results_obx x on x.unique_obr_num = r.unique_obr_num
left outer join i_lab_results_comm c on c.unique_obr_num = r.unique_obr_num
and c.obr_seq_num = r.seq_num
and c.obx_seq_num = x.obx_seq_num

[rmiao]

You can check it in execution plan.

[jezemine]

the order shouldn't really matter. what matters for getting a query like this to perform well is the indexes on all these tables, and how selective they are.

If you only join on primary keys, and you have indexes on all your foreign keys, then the plan should come out ok.