Sql 查询语句中 Where条件语句中 元操作符的运算优先顺序

SELECT prod_name, prod_price


FROM Products


WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'


AND prod_price >= 10;

prod_name              prod_price


-------------------    ----------


Fish bean bag toy      3.4900


Bird bean bag toy      3.4900


Rabbit bean bag toy    3.4900


18 inch teddy bear     11.9900


Raggedy Ann            4.9900

Look at the results above. Four of the rows returned have prices less than $10—so, obviously, the rows were not filtered as intended. Why did this happen? The answer is the order of evaluation. SQL (like most languages) processes AND operators before OR operators. When SQL sees the above WHERE clause, it reads any products costing $10 or more made by vendor BRS01, and any products made by vendor DLL01 regardless of price. In other words, because AND ranks higher in the order of evaluation, the wrong operators were joined together.

The solution to this problem is to use parentheses to explicitly group related operators. Take a look at the following SELECT statement and output:

SELECT prod_name, prod_price


FROM Products


WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')


AND prod_price >= 10;

prod_name              prod_price


-------------------    ----------


18 inch teddy bear     11.9900

The only difference between this SELECT statement and the earlier one is that, in this statement, the first two WHERE clause conditions are enclosed within parentheses. As parentheses have a higher order of evaluation than either AND or OR operators, the DBMS first filters the OR condition within those parentheses. The SQL statement then becomes any products made by either vendor DLL01 or vendor BRS01 costing $10 or greater, which is exactly what we want.

Using Parentheses in WHERE Clauses Whenever you write WHERE clauses that use both AND and OR operators, use parentheses to explicitly group operators. Don't ever rely on the default evaluation order, even if it is exactly what you want. There is no downside to using parentheses, and you are always better off eliminating any ambiguity.

转载于:https://www.cnblogs.com/alexusli/archive/2009/02/17/1392270.html