Michael Maddison
michael at ddisolutions.com.au
Mon Oct 3 01:51:05 CDT 2011
Hi Alan,
Mixing AND and ORs usually requires () to get right output. Is this not
the purpose of the demonstration :-)
>From the help file, precedence -- logical operators
SELECT ProductID, ProductModelID
FROM AdventureWorks.Production.Product
WHERE ProductModelID = 20 OR ProductModelID = 21 AND Color = 'Red'
You can change the meaning of the query by adding parentheses to force
evaluation of the OR first. The following query finds only products
under models 20 and 21 that are red.
Copy Code
SELECT ProductID, ProductModelID
FROM AdventureWorks.Production.Product
WHERE (ProductModelID = 20 OR ProductModelID = 21) AND Color = 'Red'
Using parentheses, even when they are not required, can improve the
readability of queries and reduce the chance of making a subtle mistake
because of operator precedence. There is no significant performance
penalty in using parentheses. The following example is more readable
than the original example, although they are syntactically the same.
Copy Code
SELECT ProductID, ProductModelID
FROM AdventureWorks.Production.Product
WHERE ProductModelID = 20 OR (ProductModelID = 21 AND Color = 'Red')
Precedence is determined by AND/OR and ()ing, not by where the AND and
OR are in the statement...
HTH
Cheers
Michael M
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Alan
Lawhon
Sent: Sunday, 2 October 2011 11:05 PM
To: 'Discussion concerning MS SQL Server'
Subject: [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
<mailto:dba-SQLServer at databaseadvisors.com>
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
<http://databaseadvisors.com/mailman/listinfo/dba-sqlserver>
http://www.databaseadvisors.com <http://www.databaseadvisors.com>
-----
No virus found in this message.
Checked by AVG - www.avg.com <http://www.avg.com>
Version: 10.0.1410 / Virus Database: 1520/3934 - Release Date: 10/02/11