Why Multiple OR conditions are getting checked even 1st is true?

Question

I have Three OR in where condition.

The problem is that 1st condition is getting true but it also check other conditions and evaluates the last one.

declare @SearchByParam varchar(20)

set @SearchByParam= 3

Select b.BookingID, ISNULL(Convert(varchar(11),b.AppointmentDate,106),'') as AppointmentDate, isnull(ts.FromTo,'N/A') FromTo, c.CustomerName, c.VehicleRegNo, ISNULL(b.HasCustomerArrived,0) as HasCustomerArrived, ISNULL(b.IsOrderCancelled,0) as IsOrderCancelled 
        from Bookings b 
        inner join Customers c
        on c.CustomerID= b.fk_CustomerID
        left join TimeSlots ts
        ON ts.TimeSlotID= b.fk_TimeSlotID
        where 
        b.BookingID= TRY_CONVERT(int, @SearchByParam) 
        OR
        c.CustomerName like '%'+ @SearchByParam +'%' 
        OR 
        c.VehicleRegNo like '%'+ @SearchByParam +'%' 

See b.BookingID= TRY_CONVERT(int, @SearchByParam) is getting true for 3 but it also evaluates other conditions.

Why ? It should have stopped at first one.

Answer 1

You're assuming that if it's algebraically possible to short-circuit, then it should short-circuit. But that discounts the benefits of parallelism, especially when working with many rows rather than a single scalar expression (such as in C).

In order to maximise parallel operations the execution plan can be generated such that short-circuiting gives no benefit. That's why SQL is declarative rather than imperative; you declare a problem and then SQL Server creates the plan to go about solving that. (Imperative languages execute the solution you give.) - In SQL, you can't control the order of executed operations just by changing the order of your expressions.

One option to attempt to force this, is to use collapse all three expressions in to a single CASE expression instead, as that is a linear scalar operation.

1 = CASE WHEN b.BookingID = TRY_CONVERT(int, @SearchByParam) THEN 1
         WHEN c.CustomerName like '%'+ @SearchByParam +'%'   THEN 1
         WHEN c.VehicleRegNo like '%'+ @SearchByParam +'%'   THEN 1 END

That, however, dramatically limits the planner's options and you may find that performance is degraded.

EDIT:

Having read the comments added since I started writing this answer, I think you've misunderstood SQL. It's not a matter of short-circuiting. In SQL the WHERE clause is applied to each input row independently of all other rows.

For example, the following returns all rows where myfield is either 'x' or 'y'. It does not return all rows where they're 'x' and only going to search for 'y' if no occurrences of 'x' are found...

WHERE myfield = 'x' OR myfield = 'y'

-- Which is the same as...

WHERE myfield IN ( 'x', 'y' )

In your case you appear to be trying to implement dynamic search conditions. Of which there are many bad ways to do it, and only a few good ways to do it...

A simplistic "no good" way would be this...

DECLARE @SearchByParam VARCHAR(20) = '3',
        @SearchByType  INT         =  1

SELECT
    <blah>
WHERE
        (@SearchByType = 1 AND b.BookingID= TRY_CONVERT(int, @SearchByParam))
    OR  (@SearchByTYpe = 2 AND c.CustomerName like '%'+ @SearchByParam +'%' )
    OR  (@SearchByType = 3 AND c.VehicleRegNo like '%'+ @SearchByParam +'%' )

It's "not good" because if you do want to search by BookingID you've destroyed the optimiser's ability to build the query around any index.

You'd actually be better with three queries, each tailored to the different search criteria. Or perhaps dynamic SQL, where you add the necessary WHERE clause to a query string, then execute that string.

For small data volumes the above example might help you. For larger data volumes either use multiple queries dedicated to each use-case, or read this (very in depth, but very informative) article : http://www.sommarskog.se/dyn-search.html

Answer 2

You can rewrite your where clause to :

    where 
    b.BookingID= TRY_CONVERT(int, @SearchByParam) 
    OR
    (
     c.CustomerName like '%'+ @SearchByParam +'%' and b.BookingID != TRY_CONVERT(int, @SearchByParam)
     OR
     c.VehicleRegNo like '%'+ @SearchByParam +'%' and b.BookingID != TRY_CONVERT(int, @SearchByParam)
    )

Answer 3

The best explanation for this question is the one I found in 70-761 exam preparation book. SQL is an declarative language. This means that you describe what you want to get but how it should be done is left for database engine. Beacause of it you cannot assume that all statments in WHERE clasue will be processed from left to right. Engine can choose to eveluate predicates in diffrent order that you have put in your original statment.

Answer 4

As is clear from the comments and the possible duplicate answer, short-circuit boolean logic as you know from programming languages is not guarantied in sql server, so you have to user other options like this one.

All conditions can still be checked by sql server, but it does result in the same way as you expected from your logic, so it actually simulates short-circuit boolean logic but also makes sure sql server cannot create a query plan where the "short-circuit" is left out

declare @SearchByParam varchar(20)

set @SearchByParam= 3

Select b.BookingID, 
       ISNULL(Convert(varchar(11),b.AppointmentDate,106),'') as AppointmentDate, 
       isnull(ts.FromTo,'N/A') FromTo, 
       c.CustomerName, 
       c.VehicleRegNo, 
       ISNULL(b.HasCustomerArrived,0) as HasCustomerArrived,       
       ISNULL(b.IsOrderCancelled,0) as IsOrderCancelled 
from Bookings b 
  inner join Customers c
    on c.CustomerID = b.fk_CustomerID
  left join TimeSlots ts
    ON ts.TimeSlotID = b.fk_TimeSlotID
where b.BookingID = TRY_CONVERT(int, @SearchByParam) 
OR    (b.BookingID <> TRY_CONVERT(int, @SearchByParam) 
       and
       c.CustomerName like '%'+ @SearchByParam +'%' 
      )
OR    (b.BookingID <> TRY_CONVERT(int, @SearchByParam) 
       and
       c.VehicleRegNo like '%'+ @SearchByParam +'%' 
      )