In the previous post, 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.

Ok, let’s look at my Person.Contact table again, I want to look at all the Skywalkers in that table.

SELECT 
     FirstName, LastName
FROM person.Contact
WHERE
     LastName = 'Skywalker'

OUTPUT

FirstName  LastName
---------  ---------
Luke       Skywalker
Marvin     Skywalker
Michael    Skywalker

 

Looks like I have 81 Skywalkers in my Person.Contact table. Who knew, they were so prolific? Anyway, the problem with getting all 81, is I only wanted to look at Luke’s record.   Since we know the FirstName and the LastName, we can combine them into one WHERE clause using the AND operator.

The AND operator

SELECT 
     FirstName, LastName
FROM person.Contact
WHERE
     LastName = 'Skywalker'
     AND FirstName = 'Luke'

OUTPUT

FirstName  LastName
---------  ---------
Luke       Skywalker

Now we can see the one record we want.  You can chain together as many criteria as you need with the AND clause.

The OR Operator

Like I was saying before, when you use the OR operator, if either of your two tests are true, then the result will be returned.  Let’s take our last query and change the AND to an OR, and look at how the results have changed.

SELECT 
     FirstName, LastName
FROM person.Contact
WHERE
     LastName = 'Skywalker'
     OR FirstName = 'Luke'

OUTPUT

FirstName  LastName
---------  ---------
Luke       Skywalker
Luke       Foster
Marvin     Skywalker

On my server, I got 133 results. This query returns every record in Person.Contact where the first name is Luke, no matter what the last name. It also shows you every record with Skywalker as the last name, no matter what the first name is. That’s what OR does. It returns results that match either criterion.

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.  Anyone remember “Please Excuse My Dear Aunt Sally”?

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 
	Name, color, ListPrice
FROM Production.product
WHERE
	color = 'yellow'
	OR color = 'green'
	and ListPrice < 1.00

OUTPUT

Name                    color   listprice
---------------------   ------  ---------
Road-550-W Yellow, 38	Yellow	1120.49
Road-550-W Yellow, 40	Yellow	1120.49
Road-550-W Yellow, 42	Yellow	1120.49

The lesson I want you to pick up here is that you have to look at your results and compare them to the logic you intend the server to follow.  If your results aren’t matching your intentions, look at the logic you’ve written.  You may need to wrap some of your criteron together, so the server understand you.

The server after all is just a machine, and it will do exactly what you tell it, even if you’re not entirely sure what you’re telling it to do.

Conclusion

Logical operators are a fundamental part of developing queries.  You’ll have to define your instructions to the server is ways the server thinks 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.

Previous: SELECT, Filtering Results

Next: Advanced LIKE clauses