In today’s lesson, you’re going to learn how to filter query results using the WHERE clause.  This clause is important as only those records matching the where clause’s conditions are returned in the query results. The objectives of today’s lesson are to:

  • Learn about various condition types, such as Equality, Range, and Membership
  • Comprehensive example with Select, Where, and Order By
  • Successfully complete the exercises

Important! Please follow along and do the examples in your database.  If you haven’t already done so, sign up for my Guide to Getting Started with SQL Server.  You get instructions on how to install the free tools and sample database.

The WHERE Clause

A where clause is a condition that must be met in order for a row to be returned in a query.  A simple example of a Select statement with a where clause is Select CustomerName from Customers Where State = ’MI’ In this example, only customers in the state of Michigan are selected.

In order for a row to be returned the expression State = ’MI’ must be true, that is State must equal ‘MI’ In general a row is included in a result when the where clause condition is true. The condition of a where clause can be made up of one or more tests.

Through the use of Boolean logic (Discussed in more Detail in Lesson 4) you can combine these tests to form complex comparisons between one or more columns or expressions. In this lesson, we’ll cover three types of conditions:  Equality, Range, and Membership.

Equality Conditions
An equality condition tests that a column or expression matches a value.  As you may expect, the equal sign is used to form equality.  It the following SQL statement we are returning all Persons whose title is Mr.

SELECT FirstName,
FROM   Person.Person
WHERE  Title = 'Mr.'

Other examples of equality conditions include:

  • Quantity = 30
  • UnitPrice = .25
  • City = ‘El Paso’

You may have noticed that numbers are written as is, however, the text is enclosed in single quotes. You can also incorporate expressions into an equality condition.  For example, if your data was inconsistent and states in the database are in various capitalization combinations like ‘mi’, ‘MI’, and ‘mI’, which are all different values as far as equality is concerned, you could compare the uppercase version to be safe.  To do so your condition would be

Upper(State) = 'MI'

That way regardless of however your state was capitalized, it would always evaluate to all caps and then safely compared to the proper abbreviation of the state.


Sometimes you need to exclude a value.  To do this you can use an inequality.  The operator for this is <>.  Example of inequalities are:

  • State <> ‘MI’
  • Quantity <> 0

If you were to return all persons whose title wasn’t ‘Mr. ‘, then your query would be

SELECT FirstName,
FROM   Person.Person
WHERE  Title <> 'Mr.'

 Tip! I remember that <> stands for not equals by seeing that the < and > oppose each other.

Range Conditions

Sometimes you want to know when a value falls within a range, such as quantities greater than 10.  The range conditions we are most interested in are greater than, less than, and between. The operators to perform the various test are:

  • > (greater than)
  • >=  (greater than or equal to)
  • < (less than)
  • <= (less than or equal to)

Consider our Purchasing.PurchaseOrderDetail table.  If you want to know orders where the order quantity is more than 10, then test using

OrderQty > 10

To include order quantities of 10 test using

OrderQty >= 10

This work just as well with text as with numbers.  The condition

LastName < 'Nixon'

returns all persons whose last name is before Nixon.  To include Nixon you would write

LastName <= 'Nixon'

Thus to find all persons with the last name less than or equal to Nixon you would write

SELECT FirstName,
FROM   Person.Person
WHERE  LastName <= 'Nixon'

You can also use expressions.  To find all OrderDetails with a TotalPrice more than 100 dollars, your query would look like

SELECT PurchaseOrderID,
       UnitPrice * OrderQty AS TotalPrice
FROM   Purchasing.PurchaseOrderDetail
WHERE  UnitPrice * OrderQty >= 100

But what if you want to TotalPrices that fall within 100 and 200 dollars? How can you pull this one-off?  Well, there are actually a couple of ways, but the one we’ll talk about now is the Between operator. The between operator is used in a condition as

UnitPrice * OrderQty BETWEEN 100 and 200

As you probably suspect, you can also use it for a test as well.  If you’re looking for all the states between a and c you would

States BETWEEN 'A' and 'CZ'

In the above example I used 'CZ' since all state abbreviations are two characters and I know all states abbreviations, such as, Connecticut's (CT), is before CZ.

Back to our TotalPrice example, the full statement for these queries is

SELECT PurchaseOrderID,
       UnitPrice * OrderQty AS TotalPrice
FROM   Purchasing.PurchaseOrderDetail
WHERE  UnitPrice * OrderQty BETWEEN 100 AND 200

Note that the BETWEEN operator is inclusive, that is it includes both the beginning and ending value.

!Trivia: Are some versions of SQL you can use a column alias, such as TotalPrice, in the WHERE clause.  Unfortunately, that isn’t the case with SQL Server.

Membership Conditions

The last condition type to learn about in this lesson is the membership type.  The membership type condition allows you to conduct multiple match tests compactly in one statement.  For instance, consider if you have a couple of JobTitles you want to include in a query result.   In this case, you could use the in operator to find all matches

JobTitle IN ('Accountant', 'Buyer', 'Stocker')

The above will match or return if the JobTitle title is either ‘Accountant’, ‘Buyer’, or ‘Stocker’.  To use the IN comparison operator separate the items you wish to test for with commas and be sure to enclose them in parenthesis.  The full SQL statement for our example is

SELECT NationalIDNumber,
FROM   HumanResources.Employee
WHERE  JobTitle IN ('Accountant', 'Buyer', 'Stocker') 

Comprehensive Example

Now let’s hook it all together with an example to both filter and sort a query. The overall structure for the select statement is

SELECT columns
FROM Table
WHERE WhereClause
ORDER BY Columns

In the prior section, the query to select contacts who were owners is:

SELECT NationalIDNumber,
FROM   HumanResources.Employee
WHERE  JobTitle IN ('Accountant', 'Buyer', 'Stocker')

To sort by JobTitle just add an ORDER BY clause as

SELECT   NationalIDNumber,
FROM     HumanResources.Employee
WHERE    JobTitle IN ('Accountant', 'Buyer', 'Stocker')


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

  1. Select PurchaseOrderDetailID, due date, and order quantity for all purchase order detail items whose order quantity is less than 100
  2. Select PurchaseOrderDetailID, due date, and order quantity for all purchase order detail items whose order quantity is between 1 and 10
  3. Find all Persons whose first name is “Ken”
  4. Find all productIDs from PurchaseOrders  421, 424, 142

Answers are Here!

Congratulations!  You just learned how to filter query results using the where clause.  More tutorials are to follow! Remember!  I want to remind you all that if you have other questions you want answering, 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.

  • Hi,
    I more than often have a problem with executing queries and was wondering if it is a common problem with Microsoft SQL Server and its Management Studio.
    When I try to execute a query with a table name, I receive an error message saying that it is an invalid object name (table name, namely). The query syntax is not an issue because the table name I use is exactly what appears in the Object Explorer. Please advise.

    • Hi Lana,

      When you create a new query in Microsoft SQL Server Management Studio (SSMS) you need to let SQL Server know which database to use. If you don’t have a database selected in the database explorer, chances are it selects Master and your query will fail. One way to get to the right database is to select it from the Available Databases drop dowwn. This tool is shown when you open a query window

      The picture below highlights this.

      Select Database

  • Hi Kris,
    I am new to subqueries and have managed what I wanted to but I now cannot use the results of that subquery in a calculation in the primary Select statement not the preferred option of in the where clause.
    Here is the code ( with a couple of attempts).
    The last line is what I would like to be able to use. This indicates faulty data that must be fixed.
    Any help would be great.

    select JOBS.F__id as JobNO, JOBS.F_Customer as Customer, JOBS.F_Price as JobPrice,
    (Select Sum(F_Price)
    from T_TN_Job_Item
    where F_Parentid = JOBS.F__id
    ) as JobItem,
    (Select Sum(F_Rating)
    from T_RT_Rate_Line
    where F_parentid = JOBS.F__id and F_Category = ‘Admin’
    ) as RateAdmin,
    (Select Sum(F_Price)
    from T_TN_Item_Movement
    where F_Item_TN_Job = Jobs.F__id and F_audit_logicaldelete1
    ) as Freight
    — ,sUM(Freight+RateAdmin), sUM(JobItem+RateAdmin)
    From T_TN_Job as JOBS
    Where (JOBS.F_PriceFreight+RateAdmin) or (JOBS.F_PriceJobItem+RateAdmin)

  • Hi Kris,

    One quick question : from the above Membership condition
    SELECT NationalIDNumber,
    FROM HumanResources.Employee
    WHERE JobTitle IN (‘Accountant’, ‘Buyer’, ‘Stocker’)
    ORDER BY JobTitle

    In the above example can we find out distinct JobTitle from the table using Membership condition.

    Can I select NationalIDNumber,OrganizationNode, JobTitle these columns with distinct JobTitle using Membership condion.

    Because when I use JobTitle IN (‘Accountant’, ‘Buyer’, ‘Stocker’) condition i am getting duplicate Jobtitles I want to fetch distinct records using Membership condition.


    • In general you can use DISTINCT. The DISTINCT will operate on the filtered result.

      In your specific situation you are not retrieving duplicates. The reason I know this is that you included the NationalIDNumber in your result. I quickly double checked using this query, which orders the data differently:

      SELECT NationalIDNumber,
      FROM HumanResources.Employee
      WHERE JobTitle IN ('Accountant', 'Buyer', 'Stocker')
      ORDER BY NationalIDNumber

  • {"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.