SQL WHERE Tutorial

By: Daniel Calbimonte   |   Updated: 2022-08-24   |   Comments   |   Related: > TSQL

Problem

SQL can be a powerful language if you know how to use it properly. One extremely important feature is narrowing down the set of data you are interacting with by using a WHERE clause. In this SQL tutorial, we will look at several different examples of how to use the WHERE clause.

Solution

The following examples in this SQL tutorial will show various ways you can use the WHERE clause to narrow down the search for records in a Microsoft SQL database. These examples only show how to do a SELECT, but these same techniques could be used for a DELETE statement or a UPDATE statement too.

We will use the AdventureWorks sample database for the examples if you want to follow along.

SQL WHERE Clause with Numbers

The following SQL statement demonstrates how to find data where the BusinessEntityID is equal to 14.  Here is the syntax:

SELECT
   BusinessEntityID,
   NationalIDNumber
FROM 
   HumanResources.Employee
WHERE -- WHERE Condition
   BusinessEntityID = 14

Here is the result set:

SQL WHERE Clause with String Data Type

If the value is a string, it requires single quotes around the data you are searching for as shown below. The following example finds employees with the job title of "Senior Design Engineer".

SELECT
   BusinessEntityID,
   NationalIDNumber,
   JobTitle
FROM
   HumanResources.Employee
WHERE 
   JobTitle = 'Senior Design Engineer'

Here is the result set:

SQL WHERE Clause with a Date Data Type

If you need to find date values, you need to use single quotes around the date as shown below. The following query finds the information of the employees whose hire date (HireDate) is December 30, 2010 (2010-12-30).

SELECT   BusinessEntityID,
   NationalIDNumber,
   HireDate
FROM
   HumanResources.Employee
WHERE 
   HireDate = '2010-12-30'

Here is the result set:

SQL WHERE Clause with a Date Range

When you need to find information in a date range, use the BETWEEN operator. The following query finds employees hired between January 30, 2010 and January 30, 2011.

SELECT
   BusinessEntityID,
   NationalIDNumber,
   Hiredate
FROM
   HumanResources.Employee
WHERE 
   HireDate BETWEEN '2010-01-30' AND '2011-01-30'

Here is the result set:

SQL WHERE Clause Using Wildcards

The LIKE operator is used to find character strings that match a pattern. The following query looks for employees that contain the value "roberto" as part of the LoginID. In this example, the % sign is the wildcard for any value. Since this is before and after "roberto" it will find any string that matches this pattern.

SELECT
   BusinessEntityID,
   NationalIDNumber,
   LoginID
FROM
   HumanResources.Employee
WHERE 
   LoginID LIKE '%roberto%'

Here is the result set:

SQL WHERE Clause to Find Strings that End with a Specific Character

The following SQL statement finds employees whose loginID ends with the number 1. Again, using the % wildcard this can be any value as long as the last character is a 1.

SELECT
   BusinessEntityID,
   NationalIDNumber,
   LoginID
FROM
   HumanResources.Employee
WHERE 
   LoginID LIKE '%1'

Here is the result set:

SQL WHERE Clause to Find the Strings that Start with a Range of Letters

The following example shows employees with a JobTitle that starts with a, b or c. Take a look at this tutorial for more pattern matching options.

SELECT
   BusinessEntityID,
   NationalIDNumber,
   JobTitle
FROM
   HumanResources.Employee
WHERE 
   JobTitle LIKE '[a-c]%'

Here is the result set:

SQL WHERE Clause to Match a List of Values

If you have several values to compare, you can use the IN logical operator to compare your value with a list of values. The values are in parentheses and separated by commas. The following example shows employees where the OrganizationLevel is 1, 3, or 4. This can also be done with string data, you just need to use single quotes around each value for the IN operator.

SELECT 
   BusinessEntityID,
   NationalIDNumber,
   LoginID,
   OrganizationNode,
   OrganizationLevel
FROM
   HumanResources.Employee
WHERE 
   OrganizationLevel IN (1,3,4)

Here is the result set:

SQL WHERE Clause with AND Logical Operator

The AND operator helps to find data where multiple conditions are true. The following example shows employees hired after January 30, 2010 and are single. You can also use an OR operator to find where one or the other value matches.

SELECT
   BusinessEntityID,
   NationalIDNumber,
   MaritalStatus,
   HireDate
FROM
   HumanResources.Employee
WHERE 
   HireDate > '2010-01-30' 
   AND MaritalStatus = 'S'

Here is the result set:

SQL WHERE Clause with a Function

The following SQL query uses the MONTH function in the where clause and shows all the employees hired in May (5th month).

SELECT
   OrganizationNode,
   OrganizationLevel,
   JobTitle,
   BirthDate,
   MaritalStatus,
   Gender,
   HireDate
FROM
   HumanResources.Employee
WHERE 
   MONTH(HireDate) = 5

Here is the result set:

SQL WHERE Clause with Two Tables

Sometimes we need to get results where values are present in different tables. In this example, we are joining the Person table with the Employee table and finding the employees whose FirstName starts with "Ja" and the gender is male.  Here is the SELECT statement with a SQL JOIN:

SELECT
   p.FirstName,
   p.LastName,
   e.Gender
FROM 
   Person.Person p
   INNER JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
WHERE 
   p.FirstName LIKE 'Ja%' 
   AND e.Gender = 'M'

Here is the result set:

SQL WHERE Clause with NULL Values

The following SELECT statement will find data where the MiddleName contains no value (IS NULL).

SELECT
   BusinessEntityID,
   PersonType,
   NameStyle,
   Title,
   FirstName,
   MiddleName
FROM
   Person.Person
WHERE 
   MiddleName IS NULL

Here is the result set:

SQL WHERE Clause with Subquery

The following SELECT statement finds Person data where the SalariedFlag is 0 in the Employee table by using a subquery.  Here is the syntax:

SELECT 
   BusinessEntityID,
   PersonType,
   NameStyle,
   Title,
   FirstName,
   MiddleName,
   LastName,
   Suffix
FROM
   Person.Person
WHERE -- WHERE Condition
   BusinessEntityID IN
      ( SELECT BusinessEntityID
        FROM HumanResources.Employee
        WHERE SalariedFlag = 0
      )

Here is the result set:

Next Steps

For more information, refer to these links:

• SQL Server MONTH Function
• Some Tricky Situations When Working with SQL Server NULLs
• INSERT INTO SQL Server Command
• SQL Aggregate Functions Having Clause, Order By, Distinct, Partition By and more in SQL Server, Oracle and PostgreSQL

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

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

View all my tips