<

SQL 101 – SELECT, Filtering Results (Part 2)

Published on
8,121 Points
2,121 Views
Last Modified:
Approved
Community Pick
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

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

Open in new window

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

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

Open in new window

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

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

Open in new window

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

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.
0
Comment
0 Comments

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Join & Write a Comment

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month