SQL: Using Parentheses To Get Expected Result
SQL Server as well as other DBMS has an order of evaluation that can throw you off. Especially when you have more than one comparison in the WHERE clause. In this example I will show you the difference between using a parentheses and not using one, and how by using parentheses can give the results that you want. Suppose you want to get the products with CategoryID 1 and 2 that are priced less than 15 dollars in the Products table in the Northwind database. Here is the query without the parentheses:
When you run the query above you would expect that all the records retrieved will have a unit price of less than $15 dollar but that is not the case. Below is the result from the query.
As you can see several records have unit price that are greater than $15 dollars
Now let's run the query with parantheses
Below is the result from the query
Now you are getting result that you've always wanted in the first place. The parentheses tells SQL Server to ignore the order of evaluation and evaluate what is in the parentheses first then evaluate the second part of the WHERE clause.
SELECT CategoryID,ProductName,UnitPrice
FROM Products
WHERE CategoryID = 1 OR CategoryID =2 AND UnitPrice < 15
When you run the query above you would expect that all the records retrieved will have a unit price of less than $15 dollar but that is not the case. Below is the result from the query.
As you can see several records have unit price that are greater than $15 dollars
Now let's run the query with parantheses
SELECT CategoryID,ProductName,UnitPrice
FROM Products
WHERE (CategoryID = 1 OR CategoryID =2) AND UnitPrice < 15
Below is the result from the query
Now you are getting result that you've always wanted in the first place. The parentheses tells SQL Server to ignore the order of evaluation and evaluate what is in the parentheses first then evaluate the second part of the WHERE clause.
Comments
Post a Comment