Evaluation in where clause with 'or'

Question

Evaluation in where clause with 'or'

blom0344

SSCrazy

Points: 2076
More actions
October 18, 2024 at 8:29 am

#4469855

Hello ,
Consider the following - much simplified - example of a conditional insert:
insert into sometable
SELECT x.[pkey]
,getdate()
,null
,'U'
,x.[lastmodify]
,x.[ritkey]
,x.[participant]
,x.[rdat]
,x.[routenr_7]
,x.[routenr_5]
,x.[status]
,x.[requested_by]
,x.[requested_by_name]
,x.[some_id]
,x.[limits]
,x.[attendant]
,x.[info]
FROM anothertable x inner join sometable y
on x.pkey = y.pkey and y.enddate is null -- active records
where x.status <> y.status or
x.requested_by <> y.requested_by or
x.limits <> y.limits or
x.attendant <> y.attendant or
x.info <> y.info
Would the evaluation 'stop' for instance if for a record combination the first one (the status check) would yield a difference?
Or would the entire list of compares always be fully evaluated?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

Since they are "or" conditions, SQL should be able to stop evaluating at the "first" one that is true. In theory, though, SQL could re-arrange the checks and make a different one first.

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".

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Although this does not address your question, I'd be tempted to try out this alternative syntax:

WHERE NOT EXISTS
(
    SELECT x.status
          ,x.requested_by
          ,x.limits
          ,x.attendant
          ,x.info
    INTERSECT
    SELECT y.status
          ,y.requested_by
          ,y.limits
          ,y.attendant
          ,y.info
);

which also handles the case where any of the columns has a NULL value.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

blom0344 SSCrazy Points: 2076 More actions · Answer 3

Okay Phil, that looks like an elegant alternative.

I'll test to check whether any performance difference may exist