In today’s lesson you’re going to learn how to query results using the WHERE clause.

The objectives of today’s lesson are to:

  • Learn to use more than one compare condition at a time using Boolean logic
  • Comprehensive example with Select, Where, and Order By

Using Multiple Conditions to Query Results

In previous lesson’s we learned how the where clause is used filter out any records where the where condition is FALSE.  Did you also know you can also string conditions together to create more complex conditions?  To do so we can use the AND, OR, and NOT operators.

These three operators are used for the most common aspects of Boolean logic.  Regardless of which operator is used the result always boils down to one of two outcomes:  TRUE or FALSE.

Where clauses become really interesting when we consider combining more than one field to filter a result.  For instance, using our sample database as an example, we may want to find large orders, such as those with a quantity greater than 10 and price greater than $5.00.  This could be written as

SELECT OrderID, ProductID, UnitPrice, Quantity
FROM OrderDetails
WHERE UnitPrice > 5.00 AND Quantity > 10;

Records are only included when both conditions are TRUE.

Now that we know how to write more complicated conditions, let’s learn more about the various Boolean operators. We’ll start with AND.

Boolean AND Operator

The AND operator returns a TRUE only if all conditions are also TRUE.  The following truth table shows all combinations of values the condition (A AND B).

Condition A Condition B Result

In SQL we can string a where clause together using to test multiple fields.  For instance if you’re looking for customers from Midland, TX you could write

WHERE State = 'TX' AND City = 'Midland'

You can also use the AND operator to create range condition, much like we do with BETWEEN.

Using our previous example of wanting to find TotalPrices that fall within $10. 00 and $20.00 dollars we would write

SELECT OrderID, ProductID, UnitPrice * Quantity AS TotalPrice
FROM OrderDetails
WHERE TotalPrice >= 10.00 AND TotalPrice <= 20.00;

This returns the same result as

SELECT OrderID, ProductID, UnitPrice * Quantity AS TotalPrice
FROM OrderDetails
WHERE TotalPrice BETWEEN 10.00 AND 20.00;

Tip! Keep in mind that Boolean logic AND doesn’t completely translate to English “and.”  For instance If were to say Search for all customers in the states of Florida and Georgia, you would most likely know I meant find all customer from either Florida or Georgia.  You wouldn’t think I meant for you to find all customers that are in both Florida and Georgia.

Boolean OR Operator

The OR operator returns a TRUE when one or more conditions are also TRUE.  Here is the Truth table for the OR operator.  You’ll see that in every case one of the conditions is true, so is the end result.

Condition A Condition B Result

The where clause to select all Customers from either Texas or Florida is

WHERE State = 'FL' OR State = 'TX'

Multiple OR clauses can be connected together to behave similar to the IN statement.  In this manner they act as a membership condition.

To find all customer that lead their company we would write

SELECT CompanyName, ContactName, ContactTitle
FROM Customers
WHERE ContactTitle = 'CEO'
   OR ContactTitle = 'Owner'
   OR ContactTitle = 'President';

This is the same as this

SELECT CompanyName, ContactName, ContactTitle
FROM Customers
WHERE ContactTitle IN ('CEO', 'Owner', 'President');

Boolean NOT Operator

Condition A Result

The not operator takes a condition and changes it to the opposite.  So given TRUE, the NOT operator changes it to FALSE.  Some examples of expressions using the NOT statement include:

  • NOT IN (‘TX’, ‘FL’) – Accept every state except Texas and Florida
  • NOT IN (‘CEO’, ‘Owner’, ‘President’) – Accept everyone that isn’t an owner.

The NOT statement can also be used in combination with AND and OR.  However, to explain this, we first need to understand which order the conditions are evaluated and how to group them together.

In other words, we need to learn about parenthesis and used them much in the same way you would use them with adding and multiplying numbers.

Combining Boolean Operators

The order Boolean operators are executed is important and isn’t arbitrary.  Much like in arithmetic, where multiplication occurs before additions, in Boolean operators, AND is evaluated before OR.

Can you tell what’s wrong in this photo?  What did they really mean?

Boolean Logic and English

In English they are trying to say that you can have your choice of soup with either a spring roll or crab Rangoon, but since the AND condition is evaluated first, the SQL engine sees these choices:

  1. Hot & Sour soup
  2. Wonton Soup
  3. Egg Drop Soup and Spring Roll
  4. Crab Rangoon

You can also use parenthesis.  The expression inside of the parenthesis is evaluated first.

Let’s say you wanted to return all customers who are not owners.  How could we do this?

ContactTitle = 'CEO'
OR ContactTitle = 'Owner'
OR ContactTitle = 'President'

Now, to get those that aren’t owners we need to reverse the logic as

NOT (ContactTitle = 'CEO'
     OR ContactTitle = 'Owner'
     OR ContactTitle = 'President')

Notice the use of parenthesis, the condition within the parenthesis are evaluated first, then the NOT condition second.

Comprehensive Example

Suppose we need to find all large OrdersDetails.  If we consider a large Order to be one where the Quantity > 100 or the UnitPrice > 10 and we want to order them by the total price.  How would we go about this?  Lets try

SELECT OrderDetailID, UnitPrice, Quantity
      ,UnitPrice*Quantity as TotalPrice
FROM OrderDetails
WHERE UnitPrice > 10.00 OR Quantity > 100
ORDER BY TotalPrice;

Now we could refine this further by then asserting that the TotalPrice is greater than 1000.

The modified query is

SELECT OrderDetailID, UnitPrice, Quantity
      ,UnitPrice*Quantity as TotalPrice
FROM OrderDetails
WHERE (UnitPrice > 10.00 OR Quantity > 100)
AND TotalPrice > 1000.00
ORDER BY TotalPrice;

I added the parenthesis to for the or statements to be evaluated before the AND; otherwise the statement would have a different result.


It’s important to practice! Use the sample database to answer these questions.

  1. Find Customers from the State of Illinois or Nevada
  2.  Select OrderID, OrderDate, and FreightCharge for all Orders whose freight charges are within the $4.00 to $7.00 range — don’t use the BETWEEN operator
  3. Find all OrderDetails whose TotalPrice is greater than 100.00 but less than 1000.00

Answers to Exercises

Correction:  In problem number two, I use the OR clause when comparing the FreightCharge, I should have used AND.

SELECT OrderID, OrderDate, FreightCharge
FROM Orders
WHERE FreightCharge >= 4.00 AND FreightCharge <= 7.00

Congratulations!  You just learned how to use multiple conditions to create more sophisticated filtering conditions.  More tutorials are to follow! Remember!  I want to remind you all that if you have other questions you want answered, then post a comment or tweet me.  I’m here to help you. What other topics would you like to know more about?

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

  • Select OrderID, OrderDate, and FreightCharge for all Orders whose freight charges are within the $4.00 to $7.00 range — don’t use the BETWEEN operator .
    >>> The solution in the video is incorrect. AND instead of OR is needed.

    Great tutorials, really looking forward to the next ones. If only you could watch out more for typos. There was a ridiculous amount in this one. :(

    • I’m really happy your’e finding the tutorials useful. That makes my day! Also, thanks for pointing out the error. I’ll look into that and get it fixed. Regarding the typos, I’ll re-proof the article and get it cleaned up.

      I want to make sure that you’re getting the best.

  • Hello,

    I am using the SQL Query Database and commands like .tables and .schema I don’t know how to use. Also, the table Customers used in these examples doesn’t exist in the Adventureworks2012 database I have?
    Great tutorials !

    • Hi,

      Many of the introductory articles are written twice: Once for SQLite and once for SQLServer. This article is for SQLite and won’t work with the AdventureWorks database.

      To use theAdventureWorks database and the examples, please follow along using the articles referenced on this page.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}


    Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

    But it doesn't have to be this way.

    I'm Putting together a free email course to help you get started learning SQL Server.