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?
Reference: Short Circuit Evaluation
For sql server the answer is yes. An experienced DBA buddy explained it simply as "A Where clause is not the same as an IF statement".
For SQL Server, I think it depends on the version but my experience with SQL Server 2000 is that it still evaluates @key = t.Key even when @key is null. In other words, it does not do efficient short circuiting when evaluating the WHERE clause.
I've seen people recommending a structure like your example as a way of doing a flexible query where the user can enter or not enter various criteria. My observation is that Key is still involved in the query plan when @key is null and if Key is indexed then it does not use the index efficiently.
This sort of flexible query with varying criteria is probably one case where dynamically created SQL is really the best way to go. If @key is null then you simply don't include it in the query at all.
I don't believe that short circuiting in SQL Server (2005) is guaranteed. SQL Server runs your query through its optimization algorithm that takes into account a lot of things (indexes, statistics, table size, resources, etc) to come up with an effective execution plan. After this evaluation, you can't say for sure that your short circuit logic is guaranteed.
I ran into the same question myself sometime ago and my research really did not give me a definitive answer. You may write a small query to give you a sense of proof that it works but can you be sure that as the load on your database increases, the tables grow to be bigger, and things get optimized and changed in the database, that conclusion will hold. I could not and therefore erred on the side of caution and used CASE in WHERE clause to ensure short circuit.
I think this is one of the cases where I'd write it as if it didn't short-circuit, for three reasons.
Because for MSSQL, it's not resolved by looking at BOL in the obvious place, so for me, that makes it canonically ambiguous.
because at least then I know my code will work. And more importantly, so will those who come after me, so I'm not setting them up to worry through the same question over and over again.
I write often enough for several DBMS products, and I don't want to have to remember the differences if I can work around them easily.
Main characteristic of short circuit evaluation is that it stops evaluating the expression as soon as the result can be determined. That means that rest of expression can be ignored because result will be same regardless it is evaluated or not.
Binary boolean operators are comutative, meaning:
a AND b == b AND a
a OR b == b OR a
a XOR b == b XOR a
so there is no guarantee on order of evaluation. Order of evaluation will be determined by query optimizer.
In languages with objects there can be situations where you can write boolean expressions that can be evaluated only with short circuit evaluation. Your sample code construction is often used in such languages (C#, Delphi, VB). For example:
if(someString == null | someString.Length == 0 )
printf("no text in someString");
This C# example will cause exception if someString == null because it will be fully evaluated. In short circuit evaluation, it will work every time.
SQL operates only on scalar variables (no objects) that cannot be uninitialized, so there is no way to write boolean expression that cannot be evaluated. If you have some NULL value, any comparison will return false.
That means that in SQL you cannot write expression that is differently evaluated depending on using short circuit or full evaluation.
If SQL implementation uses short circuit evaluation, it can only hopefully speed up query execution.
i don't know about short circuting, but i'd write it as an if-else statement
if (@key is null)
begin
SELECT *
FROM Table t
end
else
begin
SELECT *
FROM Table t
WHERE t.Key=@key
end
also, variables should always be on the right side of the equation. this makes it sargable.
You have to keep in mind how databases work. Given a parameterized query the db builds an execution plan based on that query without the values for the parameters. This query is used every time the query is run regardless of what the actual supplied values are. Whether the query short-circuits with certain values will not matter to the execution plan.
I typically use this for optional parameters. Is this the same as short circuiting?
SELECT [blah]
FROM Emp
WHERE ((@EmpID = -1) OR (@EmpID = EmpID))
This gives me the option to pass in -1 or whatever to account for optional checking of an attribute. Sometimes this involves joining on multiple tables, or preferably a view.
Very handy, not entirely sure of the extra work that it gives to the db engine.
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.