The short circuit coding feature saves CPU cycles and prevents runtime errors
Editor's Note: Thanks to the many readers who have submitted T-SQL Black Belt solutions! You'll see the first of the reader-contributed solutions in the October issue. If you'd like to contribute to the Black Belt section, send a description of your problem and the T-SQL code that finally did the trick to blackbelt@sqlmag.com. If we use your submission, you'll receive $100 and an exclusive T-SQL Black Belt shirt.
Some 3GLs such as the C language have an interesting feature called short circuit, which aborts any further processing of a logical expression as soon as the system can determine the expression's results. To understand how short circuit works, let's first examine how a programming language evaluates expressions. Operator precedence determines the order in which the system processes parts of an expression. The system processes operators with higher precedence before operators with lower precedence. The system processes operators with the same precedence from left to right. The following list, adapted from SQL Server Books Online (BOL), shows T-SQL's operator precedence:
() Primary Grouping
+ (Positive), - (Negative), ~ (Bitwise NOT)
* (Multiply), / (Division), % (Modulo)
+ (Add), + (Concatenate), - (Subtract)
=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
^ (Bitwise Exclusive OR), & (Bitwise AND), | (Bitwise OR)
NOT
AND
ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
= (Assignment)
Now, consider the following logical expression:
1 = 0 AND 1 = 1
According to the operator precedence rule, the system will process the two equality comparisons first, and the two results will be joined with an AND operator (e.g., FALSE AND TRUE). However, systems that support short circuit will not evaluate the right part of the expression (1 = 1) at all, because the first part (1 = 0) is FALSE and the whole expression cannot evaluate to TRUE. This feature saves CPU cycles and in certain situations even prevents runtime errors. Consider the following expression:
IF (0 <> 0) AND (1/0 > 0)
PRINT 'This works!'
A system that supports short circuits won't evaluate the right part of the expression (1/0 > 0) at all if the left part is FALSE, as it is in this example. So no runtime error, such as divide by zero, occurs. The same behavior applies in expressions that use the OR operator:
IF (1 > 0) OR (1/0 > 0)
PRINT 'This works!'
Here, you can tell from the left part of the logical expression (1 > 0) that the whole expression evaluates to TRUE, so the system doesn't need to continue processing it.
Let's use a more practical example. Consider the following table:
CREATE TABLE T1(
col1 int NOT NULL,
col2 int NOT NULL)
INSERT INTO T1 VALUES(1, 1)
INSERT INTO T1 VALUES(1, 0)
INSERT INTO T1 VALUES(-1, 1)
INSERT INTO T1 VALUES(2, 1)