The WHERE Clause and Operator Words

Contact Us or call 1-877-932-8228
The WHERE Clause and Operator Words

The WHERE Clause and Operator Words

The following table shows the word operators used in WHERE conditions.

SQL Word Operators
Operator Description
BETWEEN Returns values in an inclusive range
IN Returns values in a specified subset
LIKE Returns values that match a simple pattern
NOT Negates an operation

The BETWEEN Operator

The BETWEEN operator is used to check if field values are within a specified inclusive range.

Code Sample:

SimpleSelects/Demos/Where-Between.sql
/*
Create a report showing the first and last name of all employees
whose last names start with a letter between "J" and "M". 
*/

SELECT FirstName, LastName
FROM Employees
WHERE LastName BETWEEN 'J' AND 'M';

-- The above SELECT statement is the same as the one below.

SELECT FirstName, LastName
FROM Employees
WHERE LastName >= 'J' AND LastName <= 'M';

The above SELECT statements will both return the following results:Between Results

Note that a person with the last name "M" would be included in this report, but a person's whose last name starts with "M" would not.

The IN Operator

The IN operator is used to check if field values are included in a specified comma-delimited list.

Code Sample:

SimpleSelects/Demos/Where-In.sql
/*
Create a report showing the title of courtesy and the first and
last name of all employees whose title of courtesy is "Mrs." or "Ms.". 
*/

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy IN ('Ms.','Mrs.');

-- The above SELECT statement is the same as the one below

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy = 'Ms.' OR TitleOfCourtesy = 'Mrs.';

The above SELECT statements will both return the following results:In Results

The LIKE Operator

The LIKE operator is used to check if field values match a specified pattern.

The Percent Sign (%)

The percent sign (%) is used to match any zero or more characters.

Code Sample:

SimpleSelects/Demos/Where-Like1.sql
/*
Create a report showing the title of courtesy and the first
and last name of all employees whose title of courtesy begins with "M". 
*/

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy LIKE 'M%';

The above SELECT statement will return the following results:Like Percent Results

The Underscore (_)

The underscore (_) is used to match any single character.

Code Sample:

SimpleSelects/Demos/Where-Like2.sql
/*
Create a report showing the title of courtesy and the first and
last name of all employees whose title of courtesy begins with "M" and
is followed by any character and a period (.).
*/

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy LIKE 'M_.';

The above SELECT statement will return the following results:Like Underscore Results

Wildcards and Performance

Using wildcards can slow down performance, especially if they are used at the beginning of a pattern. You should use them sparingly.

The NOT Operator

The NOT operator is used to negate an operation.

Code Sample:

SimpleSelects/Demos/Where-Not.sql
/*
Create a report showing the title of courtesy and the first and last name
of all employees whose title of courtesy is not "Ms." or "Mrs.".
*/

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE NOT TitleOfCourtesy IN ('Ms.','Mrs.');

The above SELECT statement will return the following results:Not Results

Next