SQL: Where Clause

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.

sqlwelcome.jpg

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

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s