[dba-SQLServer] Confusion Regarding Figure 3-11 in "Murach's SQL Server 2008 for Developers" Book

I've sent the following email to the folks at Murach books asking a question
about something I've run across in their book on SQL Server 2008.  Since
they took about two weeks to respond to my last inquiry, I thought it would
be faster to consult with the experts on this message board.  (If any of you
have a copy of "Murach's SQL Server 2008 for Developers" book, you can find
the two examples I'm referring to on page 104 and 105 of that book.

     

 

Dear Mike, Joel, and Bryan:

 

Hello, it's me again - Alan C. Lawhon of Huntsville, Alabama.  I'm reading
the section of chapter 3 on "How to use the AND, OR, and NOT logical
operators" and specifically two of the examples from Figure 3-11.  The two
examples I'm referring to deal with compound conditions [in WHERE clauses]
and how using parenthesis alters the order of precedence.  I've come across
something which does not make sense which I'm hoping you guys can explain.
Based on the two examples given on page 105, I constructed (and executed)
the following query.

 

USE AP

GO

SELECT InVoiceNumber, InvoiceDate, InvoiceTotal, PaymentTotal, CreditTotal,

  (InvoiceTotal - PaymentTotal - CreditTotal) AS [BalanceDue]

FROM Invoices

--

--  The following WHERE clause (with no parenthesis) returns 100 rows.  (I
have

--  deliberately commented out this WHERE clause after running and testing

--  it.  Execution of this WHERE clause produced a 100 row resultset just

--  as the fifth example on page 105 indicates it should.)

--

-- WHERE InvoiceDate > '05-01-2008' OR InvoiceTotal > 500 

--   AND InvoiceTotal - PaymentTotal - CreditTotal > 0

--

--  The above WHERE clause is very confusing as I don't understand why the

--  [BalanceDue] calculation in the AND condition portion of the WHERE

--  clause produces 89 records in the resultset where the calculated value

--  in the [BalanceDue] field is equal to 0.00?  Those 89 records should be

--  excluded, shouldn't they?  (They are excluded in the alternate WHERE

--  clause [below] which returns 11 rows and the value in the [BalanceDue]

--  calculated field is greater than 0.00 for all eleven of those records.)

--

--  Rewritten WHERE clause (using parenthesis to change order of precedence)

--  returns 11 rows - just as Figure 3-11 indicates it should.

--

WHERE (InvoiceDate > '05-01-2008' OR InvoiceTotal > 500)

  AND InvoiceTotal - PaymentTotal - CreditTotal > 0

--

--  This WHERE clause makes sense!

--

ORDER BY [BalanceDue] DESC

 

OK, here's where I'm confused.  It's the condition following the "AND"
qualifier in both versions of the WHERE clause.  It seems to me that in the
first [commented out] WHERE clause, 89 of those 100 rows should not have
been returned!  Specifically, the 89 rows where the [BalanceDue] calculated
field, (i.e. "InvoiceTotal - PaymentTotal - CreditTotal > 0"), evaluated to
0.00.  Those rows should not have been returned because the logical "AND"
condition does not evaluate to true.  (Interestingly, in the second WHERE
clause, where parenthesis is used to change the order of precedence, only 11
rows are returned - which agrees with what Figure 3-11 indicates.)  In the
second WHERE clause example, it appears that the SQL Server database engine
(or query processor) correctly evaluates the "InvoiceTotal - PaymentTotal -
CreditTotal > 0" expression and excludes the 89 rows which do not meet that
criteria.  Since the expression following the "AND" qualifier is identical
in both WHERE clauses, (and the logical expressions on both sides of an
"AND" qualifier have to evaluate to "True" for a row to be returned), I
don't think the 89 records (where the [BalanceDue] field shows a value of
0.00) should have been returned in the first WHERE clause example.  However,
it appears that the SQL Server query processor disagrees with me since it
returned those 89 records!

 

I've looked at this repeatedly and it doesn't make sense.  The logical
condition to the right of the AND portion of the first WHERE clause fails
for 89 of the rows returned.  Irrespective of whether or not parenthesis was
used (in that first WHERE clause example), those 89 rows where the
[BalanceDue] field evaluated to 0.00 should not have been included.  That is
my contention, but I must be seriously confused about this since the query
processor says those 89 rows should have been returned - along with the
other 11 rows where "InvoiceTotal - PaymentTotal - CreditTotal > 0"
evaluated to "True".

 

If you guys can help straighten me out (or "unconfuse" me on this), I will
be very grateful.

 

Sincerely,

 

Alan C. Lawhon

Huntsville, Alabama