SQL WHERE Clause Tutorial and Examples

Use the SQL WHERE Clause filter the rows returned your SELECT statement. The where clause tests each row against one or more conditions. When the conditions return TRUE, the row is included in the result.

SQL Where Clause

Here is an example where get all people whose first name is Terry.

SELECT FirstName, LastName FROM Person.Person WHERE FirstName = 'Terry' 
/* Answer */
SELECT FirstName, LastName
FROM   Person.Person
WHERE  FirstName = 'Terry'

When you use the equals sign (=) it’s called using a comparison operator. As mentioned above, when the comparison is true, then the row is included in the result.

We can also use the not equals comparison operator (<>) to find all persons whose name is not Terry. Can you write the query to find every person not named Terry?

SELECT FirstName, LastName FROM Person.Person WHERE FirstName = 'Terry' — Change this line 
/* Answer */
SELECT FirstName, LastName
FROM   Person.Person
WHERE  FirstName <> 'Terry'

SQL WHERE and IN

Here is another example using the IN operator. Notice the result includes all people whose people first name is either Terry or Terri.

SELECT FirstName, LastName FROM Person.Person WHERE FirstName IN ('Terry', 'Terri') 
/* Answer */
SELECT FirstName, LastName
FROM   Person.Person
WHERE  FirstName IN ('Terry', 'Terri')

Why don’t you try it now? Try writing a query to select all people having the first name of either Tom, Tomas, or Tomy. Sort the result by First and Last Name.

— Write Your Answer Here! 
/* Answer */
SELECT FirstName, LastName
FROM   Person.Person
WHERE  FirstName IN ('Tom', 'Tomas', 'Tommy')
ORDER BY FirstName, LastName

You can also use NOT IN in you SQL WHERE clause. It does the opposite of IN. If we want to find every person whose name isn’t Terry or Terri, we can write our SQL WHERE like so:

SELECT FirstName, LastName FROM Person.Person WHERE FirstName NOT IN ('Terry', 'Terri') 
/* Answer */
SELECT FirstName, LastName
FROM   Person.Person
WHERE  FirstName NOT IN ('Terry', 'Terri')

In this case, TRUE is returned with FirstName is not Terry or Terri. The end result is a query returning all rows not include Terry or Terri.

SQL WHERE and BETWEEN

You can also use the BETWEEN operator in a SQL WHERE clause to compare values within a range. In this example we’re finding people whose ID’s are from 1000 to 1025:

SELECT BusinessEntityID, FirstName, LastName FROM Person.Person WHERE BusinessEntityID BETWEEN 1000 and 1025 
/* Answer */
SELECT BusinessEntityID, FirstName, LastName
FROM   Person.Person
WHERE  BusinessEntityID BETWEEN 1000 and 1025

Additional Resources

If you’re looking to learn more about using the WHERE clause, then I would recommend these resources:

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

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

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.

Name*
Email*
Sign Up
>