Wednesday, September 10, 2008

Parentheses are Cheap (use them in Boolean expressions in SQL)

Here is a simple example of how a lack of parentheses will cause results that were possibly not intended:

Statement 1
  IF (1 = 2 OR 1 = 1 AND 1 = 2)
    PRINT 'True - 1'
  ELSE
    PRINT 'False - 1'
Statement 2
  IF (1 = 1 OR 1 = 2 AND 1 = 2)
    PRINT 'True - 2'
  ELSE
    PRINT 'False - 2'
The only difference between the Boolean expressions is that they evaluate to
Statement 1:
  (false OR true AND false)
Statement 2:
  (true OR false AND false)
Look at the statements. What will the results be? Is it obvious what the results will be?

The first statement is false, and the second is true.

The first statement finds that (1 = 2) is false and is followed by an OR, so it keeps going. (1 = 1) is true, but it's followed by an AND, so it keeps checking. (1 = 2) is false, so the result is false.

The second statement finds that (1 = 1) is true and is followed by an OR. It stops evaluating and returns true.

The lesson? Use parentheses to make things clear.

No comments:

Post a Comment