in vs. exists - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

in vs. exists

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-05-21 : 11:47:34

I just had a design document I did sent back after review by our senoir developer. He's pretty smart, so I'm hoping to learn from this. Here is what my initial design was:

-- Rectify AFA/NAF accidents associated with moving violations
-- Currently for UCC WA only
if exists(
select pos_id
from pos_policy
where pos_id = @pos_id
and company = '5'
and policy_state = 'WA'
)
begin

update pos_violation
set violation = 'NAF'
where pos_id = @pos_id
and violation = 'AFA'
and violation_date not in (
select pos_violation.violation_date
from pos_violation,Violation_Points
where pos_violation.pos_id = @pos_id
and pos_violation.violation != 'AFA'
and pos_violation.violation not in (
select Violation_Points.Violation
from Violation_Points
inner join pos_policy
on Violation_Points.Rate_Table = pos_policy.rate_table
where (pos_policy.pos_id = @pos_id)
and (Violation_Points.Violation_Type_Code IN (1, 3, 4, 5, 11, 12)
)
)

update pos_violation
set violation = 'AFA'
where pos_id = @pos_id
and violation = 'NAF'
and violation_date in (
select pos_violation.violation_date
from pos_violation,Violation_Points
where pos_violation.pos_id = @pos_id
and pos_violation.violation != 'NAF'
and pos_violation.violation in (
select Violation_Points.Violation
from Violation_Points
inner join pos_policy
on Violation_Points.Rate_Table = pos_policy.rate_table
where (pos_policy.pos_id = @pos_id)
and (Violation_Points.Violation_Type_Code IN (1, 3, 4, 5, 11, 12)
)
)
end

Here is what I came up with from his recommendation. What's the performance difference? Will is work the same?

-- Rectify AFA/NAF accidents associated with moving violations
-- Currently for UCC WA only
if exists(
select pos_id
from pos_policy
where pos_id = @pos_id
and company = '5'
and policy_state = 'WA'
)
begin

update pos_violation
set violation = 'NAF'
where pos_id = @pos_id
and violation = 'AFA'

update pos_violation
set violation = 'AFA'
where pos_id = @pos_id
and violation = 'NAF'
and exists (
select pos_violation.violation_date
from pos_violation,Violation_Points
where pos_id = @pos_id
and pos_violation.violation != 'NAF'
and pos_violation.violation = Violation_Points.Violation
and Violation_Points.Moving_Violation_Flag = 'Y'
)
end

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-05-21 : 11:58:06

They look pretty different... to say what the difference in performance is, we'd need to get a better idea of the schema and data.

That being said, EXISTS is usually preferable on a well-indexed table or group of tables when you have a choice between the two.

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-21 : 13:33:49

You can also replace the exists with an INNER JOIN.

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-05-21 : 14:01:43

quote:
Originally posted by Lamprey

You can also replace the exists with an INNER JOIN.

Didn't think of that but what's a great idea.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources