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
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-08-24