December 28, 2015 at 9:08 am
Hey guys, its been a while since I was in college and need a little help with getting rid of a join. Basically the query is creating a left join against a table that already appears in the from clause and I'm wanting to remove the excess join.
Here is the code before:
Select Count(*)
from dbo.MyTable AS mcrWITH (NOLOCK)
LEFT OUTER JOIN
(SELECT MCRNUS.MyTable_ID
FROM MyTable AS MCRNUS WITH (NOLOCK)
WHERE MCRNUS.REVIEW_TYPE <> 'A'
AND (MCRNUS.Col_A = 96 OR MCRNUS.Col_B = 100 OR MCRNUS.Col_C = 100 OR MCRNUS.Col_D = 100)) AS S
ON mcr.MyTable_ID= S.MyTable_ID
where S.MyTable_ID IS NULL
I tried the following but my counts aren't matching. I tried my best to remember Nand and Nor gates homework from college but my counts don't match
Select Count(*)
from dbo.G_MASTER_CODING_RESULTS AS mcrWITH (NOLOCK)
where mcr.REVIEW_TYPE = 'HCR'
OR (MCRNUS.Col_A <> 96 AND MCRNUS.Col_B <> 100 AND MCRNUS.Col_C <> 100 AND MCRNUS.Col_D <> 100)
Anyone see a way I can remove th left join and keep the same query?
December 28, 2015 at 9:18 am
Shouldn't it be like this?
SELECT COUNT(*)
FROM dbo.G_MASTER_CODING_RESULTS AS mcrWITH (NOLOCK)
WHERE mcr.REVIEW_TYPE = 'A'
AND MCRNUS.Col_A <> 96
AND MCRNUS.Col_B <> 100
AND MCRNUS.Col_C <> 100
AND MCRNUS.Col_D <> 100
December 28, 2015 at 9:30 am
Actually I was correct in the query but I just forgot to add IsNull to columns in the Where clause:
Select Count(*)
from dbo.G_MASTER_CODING_RESULTS AS mcr WITH (NOLOCK)
where mcr.REVIEW_TYPE = 'HCR'
OR (IsNull (MCRNUS.Col_A,0) <> 96 AND IsNull (MCRNUS.Col_B,0) <> 100 AND IsNull (MCRNUS.Col_C,0) <> 100 AND IsNull (MCRNUS.Col_D,0) <> 100)
December 28, 2015 at 1:18 pm
For performance reasons, you should never use ISNULL in a WHERE (or JOIN) clause, because you'll prevent possible index seeks on the underlying column. While the code below is a better longer, it might perform dramatically better if a matching index is in place and the data follows specific patterns:
Select Count(*)
from dbo.G_MASTER_CODING_RESULTS AS mcr WITH (NOLOCK)
where mcr.REVIEW_TYPE = 'HCR'
OR ((MCRNUS.Col_A IS NULL OR MCRNUS.Col_A <> 96) AND
(MCRNUS.Col_B IS NULL OR MCRNUS.Col_B <> 100) AND
(MCRNUS.Col_C IS NULL OR MCRNUS.Col_C <> 100))
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply