<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

SQL 101 – SELECT, Filtering Results (Part 2)

Published on
8,152 Points
2,152 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

Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Join & Write a Comment

Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month