Community Pick: Many members of our community have endorsed this article.

# SQL 101 – SELECT, Filtering Results (Part 2)

Published:
Updated:
In the article, I covered the WHERE clause.  You should now feel pretty comfortable limiting the number of rows you get to return based on the values in a column.  But I'm sure you've already asked "How can I limit based on two different columns?"  I'm glad you asked!

You can chain together your WHERE clause predicates by using AND or OR.  If you've had any programming experience before, you should be pretty familiar with these two.  If you choose AND, then both criteria have to be true.  If you choose OR, then only one has to be true.

Let's go back to our full table from the original SELECT post.

``````productName     price   quantity   color
-----------     -----   --------   -----
pencil          .25      100        yellow
pen             .99       73        blue
paper          1.00      500        white
Mountain Dew   1.25        8        green
``````

The AND operator

What if you wanted to know what products we had that started with the letter p and cost less than a dollar?  That's an AND statement
``````SELECT
productName
FROM products
WHERE
productName LIKE 'p%'
AND price < 1.00
``````
``````productName
-----------
pencil
pen
``````

You now see the products starting with the letter "p" and costing less than a dollar.

The OR Operator

The OR operator is the opposite of AND, if either condition is true, the row will be returned.  It won't matter if both are true, since the first condition that results in true, will cause that row to appear, and the server will stop testing that record, and move on to the next in the table.

So, if we take that knowledge and then ask to see the records in the table that start with the letter p or cost less than a dollar, we get the same result as above, since we used the OR operator, and there are still only two records that cost less than a dollar.
``````SELECT
productName
FROM products
WHERE
productName LIKE 'p%'
OR price < 1.00
``````
``````productName
-----------
pencil
pen
``````

Combining AND With OR

I'd like to issue a word of caution.  When you need to combine AND with OR, please be aware of the order in which the comparisons will be made.  This is where I introduce parentheses into my queries.  Remember this mnemonic phrase:

"Please Excuse My Dear Aunt Sally"

(PEMDAS: Paretheses, Exponentiation, Multiplication, Division, Addition, Subtraction)

That can help you remember this: Anything in parentheses will be tested first.

When you start chaining together ANDs with ORs, you're going to see results that you don't expect to see.  In those cases really study the logic you're sending to the SQL Parser.  Should two of them really be considered at the same time?

Let's look at a contrived example.  Show me all the products that are yellow or green and cost less than a dollar.  You have to really consider that logic.  Do you want to see all items that are yellow and less than a dollar and all the items green and less than a dollar?  Or do you wish to see all items less than a dollar that are yellow or green?
``````SELECT
productName
FROM products
WHERE
color = 'yellow'
OR color = 'green'
AND price < 1.00
``````
``````productName
-----------
pencil
``````
But you could easily have used parentheses, or you could have swapped the AND with the OR.  The point of this exercise is to be aware of exactly what you are asking the parser to give you.

Conclusion

Logical operators are a fundamental part of developing queries.  You'll have to define your instructions to the server in ways the server think are unambiguous.  This can be a challenge, but with the proper training and patience, you can get the server to return the exact results you want every time.  If not, you can always update your query and hit F5 again!

As always, if you have any questions send them in!  I'm here to help.
0
2,381 Views