How to use the SQL WHERE Clause with Examples

By: Hadi Fadlallah   |   Updated: 2023-03-01   |   Comments (3)   |   Related: > TSQL

Problem

With the SQL WHERE clause in SQL Server, users can filter their query results in many ways. In most cases, we do not utilize all the options the WHERE clause provides, so we tend to forget that they exist. In this SQL tutorial, we will look at several examples of how to use the WHERE clause.

Solution

The SQL WHERE clause sets a filter condition for a SQL statement. It extracts only the rows that meet the specified conditions, like retrieving all customers located in a specific area. In this SQL tutorial, we will illustrate the different use cases and options available when using the WHERE clause and demonstrate them using the AdventureWorks2017 database.

Filtering CRUD Operations

The WHERE clause could be used in different CRUD (Create, Read, Update, Delete) operations.

Filtering a SQL SELECT Statement

A WHERE clause can be added to a SELECT statement to only return the rows that match a specific condition. For example, suppose we need to retrieve the information of all persons where the first name is "Adam" stored in the Person table. In that case, we should use the following SQL syntax:

SELECT *
FROM [AdventureWorks2017].[Person].[Person]
WHERE [FirstName] = 'Adam';

Adding a filter to the SELECT statement is also allowed when inserting the data into another table, whether it is an INSERT INTO or SELECT INTO SQL query.

SELECT *
INTO Persons_Adam
FROM [AdventureWorks2017].[Person].[Person]
WHERE [FirstName] = 'Adam';
 
INSERT INTO Persons_Adam
SELECT *
FROM [AdventureWorks2017].[Person].[Person]
WHERE [FirstName] = 'Adam';

Filtering an SQL UPDATE Statement

A WHERE clause can be added to an UPDATE statement to only affect the rows that match a specific condition. For example, if we need to increment the list price of hex nuts listed in the Product table by 100, we can use the following query:

UPDATE [AdventureWorks2017].[Production].[Product]
SET [ListPrice] = [ListPrice] + 100
WHERE [Name] LIKE 'Hex Nut%';

Filtering a SQL DELETE Statement

A WHERE clause can be added to a DELETE statement to only delete the rows that match a specific condition. For example, suppose we need to delete only the rows belonging to the employees ("EM") from the Person table. In that case, we should use the following SQL command:

DELETE FROM [AdventureWorks2017].[Person].[Person]
WHERE PersonType = 'EM'

Check out these related tips:

• Basic SQL Cheat Sheet using SELECT, INSERT, DELETE and UPDATE
• SQL Server SELECT Examples
• SQL INSERT INTO SELECT Examples
• INSERT INTO SQL Server Command
• INSERT INTO SQL Server table with SELECT command
• Basic SQL UPDATE Statement with Examples
• SQL UPDATE from SELECT, JOIN or MERGE
• The T-SQL DELETE statement
• Deleting Data in SQL Server with TRUNCATE vs DELETE commands
• Best Practices For Deleting SQL Server Data
• SQL Server Data Types Quick Reference Guide

Options Available for a WHERE Clause

This section illustrates the different options for filtering operations using a WHERE clause in a SQL database.

Working with Multiple WHERE Conditions

A WHERE clause allows adding several conditions as follows:

Retrieving Rows that Meet Several WHERE Conditions. This can be done using the AND operator. For example, if we need to retrieve all employees ("EM") named "Jean", we should use the following SQL command:

SELECT *
FROM [AdventureWorks2017].[Person].[Person] 
WHERE PersonType = 'EM' AND FirstName = 'Jean'

Retrieving Rows that Meet One of Several WHERE Conditions. This can be done using the OR operator. For example, if we need to retrieve all employees ("EM") or persons named "Jean", we should use the following SQL command:

SELECT *
FROM [AdventureWorks2017].[Person].[Person] 
WHERE PersonType = 'EM' OR FirstName = 'Jean'

Using IN Operator

A WHERE clause allows filtering rows by comparing a column with a list of values using the IN operator. For example, if we need to list all employees ("EM") and salespersons ("SP") from the Person table, we can use the following SQL command:

SELECT *
FROM [AdventureWorks2017].[Person].[Person]
WHERE [PersonType] IN ('EM','SP')

Moreover, the IN operator allows reading the values from another table using a subquery. For example, suppose we are looking to retrieve all persons listed in the HumanResources.Employee table. In that case, we could use the following SQL command:

SELECT *
FROM [AdventureWorks2017].[Person].[Person]
WHERE [BusinessEntityID] IN (SELECT [BusinessEntityID] FROM [HumanResources].[Employee])

One important note is that the IN operator does not support NULL values and will not match any row if a NULL value is listed or retrieved within the IN clause.

Using EXISTS

In general, the EXISTS operator is used to test for the existence of any record in a subquery. This operator can be used within a WHERE clause to check if specific rows in a table match other rows from another table based on one or more criteria. For example, let's use the EXISTS operator to retrieve all persons listed in the HumanResources.Employee table (same as the IN operator example).

SELECT *
FROM [AdventureWorks2017].[Person].[Person] per
WHERE EXISTS (SELECT *
              FROM [AdventureWorks2017].[HumanResources].[Employee] emp 
              WHERE per.BusinessEntityID = emp.BusinessEntityID)

EXISTS allow comparing on more than one column, which is not allowed using IN.

Using NOT EXISTS

The NOT operator can be used within a WHERE clause to negate a specific condition. For example, suppose we are looking to retrieve all persons not listed in the HumanResources.Employee table. In that case, we should use the following SQL command where we place the NOT operator before the EXISTS condition:

SELECT *
FROM [AdventureWorks2017].[Person].[Person] per
WHERE NOT EXISTS (SELECT * 
                  FROM  [AdventureWorks2017].[HumanResources].[Employee] emp 
                  WHERE per.BusinessEntityID = emp.BusinessEntityID)

Handling NULL Values

To check if a column contains or doesn't contain NULL values, we should use the IS NULL and IS NOT NULL expressions. For example, to retrieve all persons whose middle names are not recorded, we can use the following command:

SELECT *
FROM [AdventureWorks2017].[Person].[Person] per
WHERE MiddleName IS NULL

In contrast, if we are looking to retrieve all persons whose middle names are recorded, we can use the following command:

SELECT *
FROM [AdventureWorks2017].[Person].[Person] per
WHERE MiddleName IS NOT NULL

Using the WHERE Clause with JOIN

When joining several tables, we can use a WHERE clause to filter using columns from one or more tables. For example, if we need to retrieve all employees named "Eric" and hired after 2010-01-01:

SELECT emp.BusinessEntityID, emp.HireDate, per.FirstName, per.LastName, emp.JobTitle
FROM [AdventureWorks2017].[Person].[Person] per 
  INNER JOIN [AdventureWorks2017].[HumanResources].[Employee] emp 
    ON per.BusinessEntityID = emp.BusinessEntityID
WHERE emp.HireDate > '2010-01-01' AND per.FirstName = 'Eric'

Note: Using fully qualified table names or aliases is recommended to prevent ambiguity of column names.

Check out these related tips:

• Getting Started with SQL JOINS
• SQL JOIN Types with Examples
• Learn about SQL Joins on Multiple Columns
• Join SQL Server tables where columns include NULL values
• SQL RIGHT JOIN Examples
• SQL LEFT JOIN Examples

Using the LIKE Operator

Since equality is not the only way to compare string values, comparing string columns may be done using the LIKE operator to achieve the following scenarios:

Matching Strings that Begin with an Expression. For example, suppose we need to retrieve all records that begin with "hex nut" within the Product table. In that case, we should use the following SQL command:

SELECT *
FROM  [AdventureWorks2017].[Production].[Product]
WHERE [Name] LIKE 'Hex Nut%';

The percentage sign is used to specify that the string may or may not contain additional characters.

Matching Strings that End with an Expression. For example, the following SQL command is used to retrieve the fifth release of each product. We need to filter the products whose name ends with 5.

SELECT *
FROM  [AdventureWorks2017].[Production].[Product]
WHERE [Name] LIKE '% 5';

In that case, the percentage sign (%) is placed at the beginning of the string as shown in the result set.

Matching Strings that Contain an Expression. Assume we are looking to retrieve all products whose names contain the word "nut":

SELECT *
FROM  [AdventureWorks2017].[Production].[Product]
WHERE [Name] LIKE '%nut%';

Check out these related tips:

• SQL LIKE Statement
• SQL Server LIKE Syntax with Wildcard Characters
• SQL LIKE Statement for Various Text Patterns

Using the BETWEEN Operator

The BETWEEN operator is used to compare numeric and date values. It allows retrieving all records where a specific column value is within a specific range. For example, the following SQL command retrieves all employees hired between 2009-01-01 and 2010-01-01:

SELECT emp.BusinessEntityID, emp.HireDate, per.FirstName, per.LastName, emp.JobTitle
FROM [AdventureWorks2017].[Person].[Person] per 
  INNER JOIN [AdventureWorks2017].[HumanResources].[Employee] emp 
    ON per.BusinessEntityID = emp.BusinessEntityID
WHERE emp.HireDate BETWEEN '2009-01-01' AND  '2010-01-01'

Check out these tips:

• SQL BETWEEN Operator for WHERE Clause, CASE, INSERT, DELETE and UPDATE statements

Full-text Search

To learn more about SQL Server full-text search and operators, you can refer to the following article: Expand search capabilities with the SQL Server FREETEXT commands

Next Steps

If you are still learning T-SQL, you can review these tutorials on MSSQLTips:

• SQL WHERE Clause Explained
• SQL Server 101 Tutorial Outline and Overview
• Learning SQL Server
• All T-SQL Tips
• SQL ORDER BY

About the author

Hadi Fadlallah is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com. He holds a Ph.D. in data science focusing on context-aware big data quality and two master's degrees in computer science and business computing.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips