SQL WHERE - Guide and Examples including BETWEEN and IN

In today’s lesson, you’re going to learn how to filter query results using the SQL 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.

SQL WHERE Clause

SQL WHERE EQUAL
SQL WHERE NOT EQUAL
Range Conditions – Greater and Less Than

SQL WHERE BETWEEN
SQL WHERE IN
Using SQL WHERE with Multiple Conditions

Combining AND, OR, and NOT Boolean Operators

SQL WHERE Clause

Use a SQL WHERE clause to specify which rows to include in your query results.

Think of the where clause of one large condition. If the condition is true, the row is returned. There are many ways you can create conditions. Before we dig into all them, let’s go over a good first example.

In this example, we’re returning everyone whose LastName is Miller:

select FirstName, LastName
from Person.Person
where LastName = 'Miller'

Notice that the WHERE clause comes after the from clause. Also, you can see the condition LastName = ‘Miller’

In order for a row to be returned the expression LastName = ’Miller’ must be TRUE.

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, which we talk about using SQL WHERE with multiple Conditions, you can combine these tests to form complex comparisons between one or more columns or expressions.

SQL WHERE EQUAL

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, LastName
FROM   Person.Person
WHERE  Title = 'Mr.'

Other examples of using where equal 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 is inconsistent, and only the first two letters of the states columns is the abbreviation, then you could test for that:

LEFT(State,2) = 'MI'

Here, the LEFT function takes the first two characters and from State and uses them in the comparison.

SQL WHERE NOT EQUAL

Sometimes you need to exclude a value. To do this you can use an inequality. To set up a Where Not Equal statement use the <> operator. 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,
       LastName
FROM   Person.Person
WHERE  Title <> 'Mr.'

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

Range Conditions – Greater and Less Than

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:

<= (less than or equal to)

Numeric Ranges

Consider our Purchasing.PurchaseOrderDetail table. If you want to know orders where the order quantity is more than 10, the following query does the trick!

select PurchaseOrderDetailID, DueDate, OrderQty, ProductID, UnitPrice
from Purchasing.PurchaseOrderDetail
where OrderQty > 10

Here you can see the results:

To include order quantities of 10 test using

OrderQty >= 10

Using Ranges with Text Values

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

select FirstName, LastName
from Person.Person
where LastName < 'Miller'

returns all persons whose last name is before Miller.

To include Miller you would write

LastName <= 'Miller'

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

select FirstName, LastName
from Person.Person
where LastName <= 'Miller'

Expressions and SQL Order of Operations

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

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

Though we define TotalPrice as a column in the SELECT statement, we can’t use it in the WHERE clause. The SQL Order of Operations doesn’t allow this.

SQL WHERE Expression Order of Operations

But what if you want to TotalPrices that fall within 100 and 200 dollars? How can you pull this one-off?

SQL WHERE BETWEEN

Well, there are actually a couple of ways, but the one we’ll talk about now is the Between operator.

SQL BETWEEN operator with a variety of types, such as integer, varchar, and dates.

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,
       PurchaseOrderDetailID,
       ProductID,
       UnitPrice,
       OrderQty,
       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.

You can more learn about SQL Between in this video.

Use BETWEEN with Text

The following SQL shows how to use BETWEEN to find all people having last names greater than or equal to Colvin and less than or equal to Conteras.

Person Table used for SQL BETWEEN example

SELECT FirstName, LastName
FROM Person.Person
WHERE LastName BETWEEN 'Colvin' and 'Contreras'

Using BETWEEN improves the readability of your SQL. Notice it eliminates several comparison operators. The above example is equivalent to:

SELECT FirstName, LastName
FROM Person.Person
WHERE LastName >= 'Colvin' AND LastName <= 'Contreras

SQL BETWEEN Operator with Dates

SQL BETWEEN is well suited for date range comparisons since it help improves your SQL’s readability.

It this example see how BETWEEN simplifies selecting every Person whose record was between January 12th – 14th, 2014 date range.

SELECT FirstName, LastName, ModifiedDate
FROM Person.Person
WHERE ModifiedDate BETWEEN '2014-01-12' AND '2014-01-14'

In this example notice how the date is within single quotes. Behind the scenes, SQL implicitly converts the “text” date to a date type.

SQL BETWEEN Operator with DateTime

When using BETWEEN with DATETIME types are tricky, especially if you’re looking to compare a DATETIME value to within an entire day.

Suppose ModifiedDate is a DATETIME type and you want to fine all Person.Person entries modified on January 12, 13, or 14th 2014.

Though it is tempting to use the same statement as before

SELECT FirstName, LastName, ModifiedDate
FROM Person.Person
WHERE ModifiedDate BETWEEN '2014-01-12' AND '2014-01-14'

This returns incorrect results, as 2014-01-14 is implicitly converted to the DATETIME value 2014-01-14 00:00:00, which is the beginning of the 14th not the end. You’ll miss an entire day’s worth of modifications!

Instead write the following SQL BETWEEN clause:

SELECT FirstName, LastName, ModifiedDate
FROM Person.Person
WHERE ModifiedDate BETWEEN '2014-01-12 00:00:00' AND '2014-01-14 23:59:59'

In summary, use the SQL BETWEEN operator to test against a range of values.

The range is inclusive.

Using SQL BETWEEN is shorthand for using >= AND <= conditionals.

SQL WHERE IN

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,
       OrganizationNode,
       JobTitle
FROM   HumanResources.Employee
WHERE  JobTitle IN ('Accountant', 'Buyer', 'Stocker')

Here are the results:

The IN clause is very handy, and serves to simplify your WHERE clause.

Learn More! If your looking to harness even more power then read my article SQL IN Operator

Comprehensive Example using IN

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,
       OrganizationNode,
       JobTitle
FROM   HumanResources.Employee
WHERE  JobTitle IN ('Accountant', 'Buyer', 'Stocker')

To sort by JobTitle just add an ORDER BY clause as

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

Here are the results.

Exercises for comparisons, BETWEEN, and IN

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

Select PurchaseOrderDetailID, due date, and order quantity for all purchase order detail items whose order quantity is less than 100

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

Answers are Here!

Using SQL WHERE with Multiple Conditions

In previous sections 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.

SQL 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 purchase orders, such as those with an order quantity greater than 10 and unit price greater than $5.00. This could be written as

SELECT PurchaseOrderDetailID,
       ProductID,
       OrderQty,
       UnitPrice
FROM   Purchasing.PurchaseOrderDetail
WHERE  UnitPrice > 5.00
       AND OrderQty > 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.

SQL 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).

A	B	Result
TRUE	TRUE	TRUE
TRUE	FALSE	FALSE
FALSE	TRUE	FALSE
FALSE	FALSE	FALSE

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.

In this example let’s calculate TotalPrices and return those that fall within $100.00 and $200.00 dollars:

SELECT PurchaseOrderDetailID,
       ProductID,
       OrderQty,
       UnitPrice,
       UnitPrice * OrderQty AS TotalPrice
FROM   Purchasing.PurchaseOrderDetail
WHERE  UnitPrice * OrderQty >= 100
       AND UnitPrice * OrderQty <= 200

The rows returned have a calculated TotalPrice within the range 100 to 200.

This returns the same result as

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

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.

SQL 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.

A	B	Result
TRUE	TRUE	TRUE
TRUE	FALSE	TRUE
FALSE	TRUE	TRUE
FALSE	FALSE	FALSE

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 employees with one of three job titles we can write

SELECT NationalIDNumber,
       BirthDate,
       JobTitle
FROM   HumanResources.Employee
WHERE  JobTitle = 'Design Engineer'
       OR JobTitle = 'Stocker'
       OR JobTitle = 'Buyer'

This is the same as this

SELECT NationalIDNumber,
       BirthDate,
       JobTitle
FROM   HumanResources.Employee
WHERE  JobTitle IN ('Design Engineer', 'Stocker', 'Buyer')

SQL NOT Operator

The not operator takes a condition and changes it to the opposite. So given TRUE, the NOT operator changes it to FALSE.

A	Result
TRUE	False
FALSE	True

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 AND, OR, and NOT Boolean Operators

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

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

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:

Hot & Sour soup
Wonton Soup

Egg Drop Soup and Spring Roll
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 Purchase Order details entries. 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   PurchaseOrderDetailID,
         ProductID,
         OrderQty,
         UnitPrice,
         UnitPrice * OrderQty AS TotalPrice
FROM     Purchasing.PurchaseOrderDetail
WHERE    (UnitPrice > 10
          OR OrderQty > 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   PurchaseOrderDetailID,
         ProductID,
         OrderQty,
         UnitPrice,
         UnitPrice * OrderQty AS TotalPrice
FROM     Purchasing.PurchaseOrderDetail
WHERE    (UnitPrice > 10
          OR OrderQty > 100)
         AND UnitPrice * OrderQty >= 1000
ORDER BY TotalPrice

I added the parenthesis around the OR clauses so they would be evaluated before the AND; otherwise the statement would have a different result.

One final comment: You notice that I used the column alias TotalPrice in the ORDER BY clause, but didn’t use it in the WHERE clause. This is due to a limitation in SQL Server. Some versions, such as SQLite would allow you to write the following, which is prohibited in SQL Server:

SELECT   PurchaseOrderDetailID,
         ProductID,
         OrderQty,
         UnitPrice,
         UnitPrice * OrderQty AS TotalPrice
FROM     Purchasing.PurchaseOrderDetail
WHERE    (UnitPrice > 10
          OR OrderQty > 100)
         AND TotalPrice >= 1000
ORDER BY TotalPrice

Combining Operators Exercises

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

Find all single female employees
List all employees that have 40 to 80 hours of vacation time.

Display all employees that have 40 to 80 hours of vacation time or 40 to 80 hours of sick time. Also, the employees should be male.

Answers are Here!

Conclusion

The SQL WHERE clause is used to filter rows from your results. Only rows satisfying the WHERE condition are kept. In terms of logic, this means the conditions evaluate to TRUE.

You can use several SQL operators to form conditions. Comparison operators, BETWEEN and IN are good starting points.

Once you are comfortable using these, you can combine them together to create more complex conditions using SQL AND, OR, and NOT.

If you want to learn more about SQL WHERE, check out these articles:

7 responses to “SQL WHERE – Guide and Examples”

david

Nov 26, 2019

why can’t I use a ” in a where, I need to use a ‘ ?

1. Kris Wenzel
  
  Dec 6, 2019
  
  In SQL Server, a single quote is used to delimit text.
  
  If you want to learn more about single quote, and how you can embed them in your SQL text check out my article: How to include a single quote in a SQL query
  
Sana

Dec 12, 2018

Hi Kris,

One quick question : from the above Membership condition
SELECT NationalIDNumber,
OrganizationNode,
JobTitle
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.

Thanks,
Sana

1. Kris Wenzel
  
  Dec 12, 2018
  
  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, OrganizationNode, JobTitle FROM HumanResources.Employee WHERE JobTitle IN ('Accountant', 'Buyer', 'Stocker') ORDER BY NationalIDNumber
  
Gray

Oct 16, 2018

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.
Thanks,
Gray.

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)

Lana

Feb 4, 2015

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.
Thanks,
Lana

1. Kris Wenzel
  
  Feb 5, 2015
  
  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.

SQL WHERE – Guide and Examples

Table of contents