How to prioritize the operations of the operators in the WHERE clause in SQL query statements

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, obviusly, the rows were not filtered as intended. Why did this happen? The answer is the order of evaluation. SQL (like most versions ages) ProcessesAndOperators beforeOrOperators. When SQL sees the aboveWhereClause, it reads any products costing $10 or more made by vendorBrs01, And any products made by vendorDll01Regardless of price. In other words, becauseAndRanks 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 followingSelectStatement 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 thisSelectStatement and the earlier one is that, in this statement, the first twoWhereClause conditions are enclosed within parentheses. As parentheses have a higher order of evaluation than eitherAndOrOrOperators, the DBMS first filtersOrCondition within those parentheses. The SQL statement then becomes any products made by either vendorDll01Or vendorBrs01Costing $10 or greater, which is exactly what we want.

Using parentheses inWhereClauses whenever you writeWhereClses that use bothAndAndOrOperators, 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.