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.
Table of contents
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:
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.

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.

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
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.
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.
Read More
If you want to learn more about SQL WHERE, check out these articles:
Leave a Reply