December 20, 2011 at 1:42 pm
The left joins were used to match the execution plan of the original query I was able to dig up. I tested both with and without the where clause to make sure my rewrite matched the original both ways.
The report I am converting uses only matching data -- "Missing" PO information is not included. For the report, they can all be inner joins.
If I were to run the query without the PO_Type restriction(s), I am OK with the NULLs but can adjust as needed.
Thank you
December 21, 2011 at 12:47 am
Don't focus on whether the plan looks the same, SQL can generate very different plans for queries and, unless you're examining the properties of all the operators, two queries that return different data can have plans that look the same.
Focus on whether it returns the same data as the original query.
If you only want matching data, turn those left outer joins into inner joins (you're essentially doing that with the filters anyway, so make it explicit)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2011 at 10:50 am
The journey to the execution plan was a result of the first comment.
My initial re-write is close to what I would want for general purposes:
I want the jobs & job_operations (jobs that have operations and not those without) -- inner
Jobs (& Operations) that are either internal or external (Source) -- left outer on Source (NULL are OK -- no outside work). If no outside work, there is no need for PO information.
My initial post, with the where clause, produced the desired results with the limited data we have. However, I wanted to be sure I wasn't missing something.
Thanks
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply