Is the SQL Where Clause Short-Circuit Evaluated

Is the SQL WHERE clause short-circuit evaluated?

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.

Does SQL short-circuit the evaluation of an OR in the WHERE CLAUSE?

Whether or not SQL Server short circuits your particular expression isn't relevant here, it will generate the correct result either way. In this case, short circuiting is merely an optimization.

And as for optimizations, it's up to how your index is built. These are (ideally) index seeks.

Is MS-SQL AND/OR conditional (perform short-circuit evaluation)?

There is no guarantee of this behaviour.

An example of short circuiting evaluation not happening with expr1 AND expr2 is

SET STATISTICS IO ON

IF EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2)  
AND EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1)
PRINT 'Y'

The EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2) is false (meaning the And-ed expression must be False) but the IO results show the second condition was still evaluated.

Table 'spt_values'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'spt_monitor'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server can do this though. I see this in my test

SET STATISTICS IO ON

DECLARE @p1 BIT = NULL

IF ( @p1 = 1
     AND EXISTS(SELECT *
                FROM   master..spt_values) )
  PRINT '1'

ELSE IF ( @p1 = 0
     AND EXISTS(SELECT *
                FROM   master..spt_values) )
  PRINT '2'

The output is

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Showing spt_values was never accessed.

This is implemented by a pass through predicate condition in the execution plan. There is some information about those here.

If the passthru predicate evaluates to true, the join returns the row
.... If the passthru
predicate evaluates to false, the join proceeds normally

How to Short-Circuit SQL Where Clause

SQL Server does not do short-circuiting (nor should it).

If you need it to not try something under some circumstances, you need to force that in the way that you write your query.

For this query the easiest fix would be to use a CASE expression in your WHERE clause.

declare @queryWord as nvarchar(20) = 'asdas'

SELECT  * FROM TABLE_1 
WHERE TABLE_1.INIT_DATE = (CASE WHEN ISDATE(@queryWord) = 1 
                                THEN CONVERT(Date, @queryWord)
                           ELSE NULL  END)

Off-hand, CASE and query-nesting are the only two supported ways that I can think of to force an order of evaluation for dependent conditions in SQL.

OR Operator Short-circuit in SQL Server

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.

Does SQL short-circuit the evaluation of an OR in the WHERE CLAUSE?

Whether or not SQL Server short circuits your particular expression isn't relevant here, it will generate the correct result either way. In this case, short circuiting is merely an optimization.

And as for optimizations, it's up to how your index is built. These are (ideally) index seeks.

Does Oracle use short-circuit evaluation?

It depends.
.
In general, Oracle does not guarantee that a SQL statement will use short-circuit evaluation (though PL/SQL is guaranteed to perform short-circuit evaluation). The Oracle optimizer is free to evaluate the predicates in whatever order it expects to be most efficient. That might mean that the first predicate is evaluated first and only the matching rows have the second predicate evaluated but it is entirely possible that either the reverse happens or that Oracle transforms the query into a sort of UNION and fully evaluates both predicates before combining the results.

That being said, if the optimizer can determine at compile time that a predicate will always evaluate to TRUE or FALSE, the optimizer should just treat that as a constant. So if, for example, there is a constraint on the table that prevents X from ever having a value of 'true', the optimizer shouldn't evaluate the second predicate at all (though different versions of the optimizer will have different abilities to detect that something is a constant at compile time).

As for the second part of your question, without seeing the query plans, it's very hard to tell. The Oracle optimizer tends to be pretty good at transforming queries from one form to another if there are more efficient ways of evaluating it. In general, however, if subQ is going to return a relatively large number of rows compared to table, it may be more efficient to structure the query as an EXISTS rather than as an IN.

Does SQL Server short-circuit IF statements?

Even if it appears to work, it should not be relied upon. The CASE statement is the only thing that the documentation states as being short-circuiting, but even that isn't (or at least wasn't) always the case (hee hee). Here is one bug that was fortunately fixed as of SQL Server 2012 (see the comments).

In addition to the rabbit hole (an interesting one, for sure) of links in comments from the comment posted by @Martin on the question, you should also check out this article:

Understanding T-SQL Expression Short-Circuiting

and the discussion forum related to that article.

---

Related Topics