The Where clause in SQL works as a filter. In this lesson we will be using MS SQL Server and Microsoft’s free database example Adventure Works 2012.
If you want to follow along with the exercises:
MS SQL Server installation instructions here: MS SQL Server: Installation
Instructions for loading Adventure Works: SQL: SELECT Statement
We are going to be using some selected fields from the table HumanResouces.Employee in the AdventureWorks2012 database.
Find single value
Let’s use Where to find the record with the BusinessEntityID of 6:
SELECT [BusinessEntityID]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[SalariedFlag]
FROM [AdventureWorks2012].[HumanResources].[Employee]
Where BusinessEntityID = 6
To find a single matching numeric field, use Where Field = Number
You can also use the following operators
- = equals
- > greater than
- < less than
- != not
Look for String
To find a string value, we are going to use the Like keyword: Where Field like ‘String’
SELECT [BusinessEntityID]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[SalariedFlag]
FROM [AdventureWorks2012].[HumanResources].[Employee]
Where JobTitle like ‘Design Engineer’
Using a Wildcard
When trying to search by part of a string, we use the % wildcard.
Consider the sentence “Dogs love chasing cats”
- ‘Dog%’ = “Dogs love chasing cats”
- ‘%cats’ = “Dogs love chasing cats”
- ‘%love%’ = “Dogs love chasing cats”
Below we use a wildcard to find all job titles that end in Engineer
SELECT [BusinessEntityID]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[SalariedFlag]
FROM [AdventureWorks2012].[HumanResources].[Employee]
Where JobTitle like ‘%Engineer’
Between
The Between keyword can used to find records that fall between 2 values
SELECT [BusinessEntityID]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[SalariedFlag]
FROM [AdventureWorks2012].[HumanResources].[Employee]
Where BusinessEntityID between 5 and 20
And, Or
This query returns all Married employees who are Female
SELECT [BusinessEntityID]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[SalariedFlag]
FROM [AdventureWorks2012].[HumanResources].[Employee]
Where MaritalStatus = ‘M’ and Gender = ‘F’
This returns all employees who are married or employees who are F regardless of martial status
SELECT [BusinessEntityID]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[SalariedFlag]
FROM [AdventureWorks2012].[HumanResources].[Employee]
Where MaritalStatus = ‘M’ or Gender = ‘F’
()
Parenthesis are used just like your remember from Algebra class. They state that the items inside must performed first.
This query returns all Married Males as well as all Tool Designers regardless of gender or marital status.
SELECT [BusinessEntityID]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[SalariedFlag]
FROM [AdventureWorks2012].[HumanResources].[Employee]
Where (MaritalStatus = ‘M’ and Gender = ‘M’) or JobTitle like ‘Tool Designer’
If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT.
Follow this link for more SQL content: SQL