Is the SQL WHERE clause short-circuit evaluated?

sql short-circuiting
Greg Dean picture Greg Dean · Apr 25, 2009 · Viewed 50.2k times · Source

Are boolean expressions in SQL WHERE clauses short-circuit evaluated ?

For example:

SELECT * 
FROM Table t 
WHERE @key IS NULL OR (@key IS NOT NULL AND @key = t.Key)

If @key IS NULL evaluates to true, is @key IS NOT NULL AND @key = t.Key evaluated?

If No, why not?

If Yes, is it guaranteed? Is it part of ANSI SQL or is it database specific?

If database specific, SqlServer? Oracle? MySQL?

Answer

Mert Sonsuz picture Mert Sonsuz · May 26, 2009

ANSI SQL Draft 2003 5WD-01-Framework-2003-09.pdf

6.3.3.3 Rule evaluation order

[...]

Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from left to right. However, it is implementation-dependent whether expressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression.

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More