SQL 101 – SELECT, Filtering Results (Part 2)

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

Select all Open in new window

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

Select all Open in new window

productName
                      -----------
                      pencil
                      pen

Select all Open in new window

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

Select all Open in new window

productName
                      -----------
                      pencil
                      pen

Select all Open in new window

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

Select all Open in new window

productName
                      -----------
                      pencil

Select all Open in new window

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.