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
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_value

Open in new window

patd1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
I believe it would be
SELECT
      *
FROM pt WITH (NOLOCK)
      LEFT JOIN pu WITH (NOLOCK) ON pt.policy_key = pu.policy_key
                  AND pt.punit_key = pu.punit_key

      LEFT JOIN pc WITH (NOLOCK) ON pt.policy_key = pc.policy_key
                  AND pt.pcov_key = pc.pcov_key

      INNER JOIN py WITH (NOLOCK) ON ((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))

      INNER JOIN cv WITH (NOLOCK) ON py.period_key = cv.varchar_value
                  AND cv.name = 'load_period'

WHERE pt.accountingperiodID IS NOT NULL
      AND pt.gross_written_premium <> 0
      AND pt.bill_group_ind = '1'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
By the way NEVER try to mix the join syntax styles, that is a recipe for bugs to creep in.
0
patd1Author Commented:
Will 'OR' work on the join?
I read it somewhere to do inner joins first, then left outer joins. Does that matter?

Thank you.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

PortletPaulfreelancerCommented:
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.
0
patd1Author Commented:
Thank you.  My server is down at this time. I will try an and let you know.
0
PortletPaulfreelancerCommented:
use execution plans for the comparison by the way, I meant to mention that.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.