Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

OR Operator Short-circuit in SQL Server

Tags:

I want to consult SQL Server OR short-circuit

Code:

DECLARE @tempTable table
    (
        id int
    )
INSERT @tempTable(id) values(1)
      
DECLARE @id varchar(10)
SET @id = 'x'
SELECT * FROM @tempTable WHERE 1=1 OR id = @id --successfully
SELECT * FROM @tempTable WHERE @id = 'x' OR id = @id --Exception not Convert 'x' to int

Why? 1=1 and @id='x' are true.

SQL Server OR operator : whether the short-circuit function?

THANKS

like image 297
NotTwoWayStreet Avatar asked Jun 27 '12 05:06

NotTwoWayStreet


People also ask

Does or short-circuit in SQL?

SQL Server does short-circuit sometimes, but the rules are complex, undocumented, and subject to change at any time.

Does or operator short-circuit?

The || OR operator is also a short-circuit operator. Since OR evaluates to true when one or both of its operands are true , short-circuit evaluation stops with the first true . The OR operator comes in a non-short-circuit version as well: | (this is a single vertical bar.)

What is or operator in SQL?

The SQL AND, OR and NOT Operators The AND and OR operators are used to filter records based on more than one condition: The AND operator displays a record if all the conditions separated by AND are TRUE. The OR operator displays a record if any of the conditions separated by OR is TRUE.


1 Answers

Within SQL, there is no requirement that an OR clause breaks early. In other words, it is up to the optimizer whether to check both conditions simutaneously. I am not an expert in the MSSQL optimizer, but I have seen instances where the optimizer has and has not short circuited an OR clause.

like image 82
Steven Mastandrea Avatar answered Sep 20 '22 10:09

Steven Mastandrea