converting a query with implicit join to explicit inner and outer join
I have a sql query that uses implicit join on 4 tables, I want two of those joins to be left outer join. Please see embeded code snippet and suggest how I can set the whole query to use left outer join between pt and pu AND pt and pc.
Thanks
Thanks
SELECT pt.policy_key,
pt.pcov_key,
pt.punit_key,
pu.riskstateID,
pc.coverage_code,
pc.coverageID,
pc.lobID,
pt.gross_written_premium,
py.period_key,
pt.trans_eff_date,
pt.trans_eff_period,
pt.trans_exp_date,
pt.trans_exp_period,
pt.extracted_date,
pt.extracted_period,
py.period_end_date,
pt.commission_amt,
pt.transtypeID,
pt.newvsrenewalID,
pc.multidiscountID,
pc.affinitydiscountID,
pc.affinitydiscounttypeID,
pc.contentreplacementID,
pc.ERCID,
pc.deductibleID,
pc.limitID,
pt.nonrenewalreasonID,
pc.feetypeID,
pu.zipID,
pu.occupancytypeID,
pu.roofshapeID,
pu.roofmaterialID,
pu.yearbuiltID,
pu.ratingterritoryID,
pu.ratingtierID,
pu.formID,
pu.numberoffamiliesID,
pu.constructiontypeID,
pu.deductibletypeID
FROM pt WITH(nolock),
pu WITH(nolock),
pc WITH(nolock),
py WITH(nolock),
cv WITH(nolock)
WHERE pt.policy_key = pu.policy_key
AND pt.punit_key = pu.punit_key
AND pt.policy_key = pc.policy_key
AND pt.pcov_key = pc.pcov_key
AND ( ( pt.trans_eff_date <= py.period_end_date
AND pt.trans_exp_period >= py.period_key
AND pt.extracted_date <= py.period_end_date )
OR ( pt.extracted_period > pt.trans_exp_period
AND pt.extracted_period = py.period_key ) )
AND pt.accountingperiodID IS NOT NULL
AND pt.gross_written_premium <> 0
AND pt.bill_group_ind = '1'
AND cv.name = 'load_period'
AND py.period_key = cv.varchar_valueASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
PortletPaul
By the way NEVER try to mix the join syntax styles, that is a recipe for bugs to creep in.
ASKER
Will 'OR' work on the join?
I read it somewhere to do inner joins first, then left outer joins. Does that matter?
Thank you.
I read it somewhere to do inner joins first, then left outer joins. Does that matter?
Thank you.
Yes: 'OR' will work in a join, so do >, <, <=, >= and so on
regarding this: should we do INNER JOIN before LEFT JOIN
Wow, that's a much bigger topic. The thing is that SQL isn't necessarily executed in the exact sequence that we write it.
So in theory it does not matter and it is up to the "optimizer" to decide how best to organize the execution plan. In practice sometimes the optimizer doesn't choose a plan that seems the most logical or efficient (and this can be affected by all sorts of factors such as stale statistics or missing indexes that you thought were or should be there).
My advice would be to try it. It should not make any noticeable difference in performance but maybe it will I cannot really predict that. However you might find that the logic (inners before outers) to be useful for maintainability reasons.
regarding this: should we do INNER JOIN before LEFT JOIN
Wow, that's a much bigger topic. The thing is that SQL isn't necessarily executed in the exact sequence that we write it.
So in theory it does not matter and it is up to the "optimizer" to decide how best to organize the execution plan. In practice sometimes the optimizer doesn't choose a plan that seems the most logical or efficient (and this can be affected by all sorts of factors such as stale statistics or missing indexes that you thought were or should be there).
My advice would be to try it. It should not make any noticeable difference in performance but maybe it will I cannot really predict that. However you might find that the logic (inners before outers) to be useful for maintainability reasons.
ASKER
Thank you. My server is down at this time. I will try an and let you know.
use execution plans for the comparison by the way, I meant to mention that.