Alan Lawhon
lawhonac at hiwaay.net
Tue Oct 4 22:02:07 CDT 2011
Asger:
I spent quite a bit of time experimenting with that first (confusing) WHERE clause (the one without parentheses) before it became obvious that the first condition prior to the "OR" operator, (i.e. WHERE InvoiceDate > '05-01-2008'), was being evaluated first and records meeting that condition were included in the result set. What threw me (in both of the WHERE clauses) was the BalanceDue calculation following the "AND" logical operator. My understanding of the AND logical operator is that both expressions (on both sides of the AND operator) must evaluate to "True" in order for a record to be included in the result set. In the first WHERE clause, since the "InvoiceTotal - PaymentTotal - CreditTotal > 0" evaluated to "False" for 89 of the records returned, it didn't make sense to me that those 89 records should be in the result set. Using the parenthesis in the second WHERE clause made much better sense since those 89 records - where the BalanceDue calculation was not > 0 - were excluded from the result set.
I suppose the point the Murach book was trying to get across is that you need to be really careful about how you use logical operators in WHERE clauses. Thanks for taking a look at this.
Alan C. Lawhon
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Tuesday, October 04, 2011 6:51 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Confusion Regarding Figure 3-11 in "Murach's SQLServer 2008 for Developers" Book
Hi Alan,
The condition:
WHERE InvoiceDate > '05-01-2008' OR InvoiceTotal > 500 AND InvoiceTotal - PaymentTotal - CreditTotal > 0
Is equivalent to:
WHERE InvoiceDate > '05-01-2008' OR (InvoiceTotal > 500 AND InvoiceTotal - PaymentTotal - CreditTotal > 0)
That's why you get all records where InvoiceDate is greater than '05-01-2008', no matter what the InvoiceTotal or the BalanceDue is. The 89 records which are bothering you (having a BalanceDue equal to 0.00) are included in the result set because they satisfy the condition InvoceDate > '05-01-2008'.
Your rewritten where clause has a quite different meaning:
WHERE (InvoiceDate > '05-01-2008' OR InvoiceTotal > 500) AND InvoiceTotal - PaymentTotal - CreditTotal > 0.
This condition says: all records having a BalanceDue greater than 0 and either an InvoiceDate greater than '05-01-2008' or an InvoiceTotal greater than 500.
I agree that the condition as written in the book (which I haven't read) might be confusing. I would prefer the equivalent expression using parenthesis a shown above.
Asger
-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Alan Lawhon
Sendt: 2. oktober 2011 14:05
Til: 'Discussion concerning MS SQL Server'
Emne: [dba-SQLServer] Confusion Regarding Figure 3-11 in "Murach's SQLServer 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
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com