Evaluation in where clause with 'or'

  • 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?

     

     

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

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Okay Phil, that looks like an elegant alternative.

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

     

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

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