Refining What You See Using WHERE

The type of SELECT statement we’ve worked with so far retrieves all the rows from a given table and uses them in the statement’s result set.
This is great if you really do need to see all the information the table contains.
But what if you want to find only the rows that apply to a specific person, a specific place, a particular numeric value, or a range of dates?
You might, for example, have a need to ask the following types of questions:

“Who are our customers in Seattle?”
“Show me a current list of our Bellevue employees and their phone numbers.”
“What kind of music classes do we currently offer?”
“Give me a list of classes that earn three credits.”
“Give me the names of our staff members who were hired on May 16, 1985.”

The WHERE Clause

You use a WHERE clause in a SELECT statement to filter the data the statement draws from a table.
The WHERE clause contains a search condition that it uses as the filter.
This search condition provides the mechanism needed to select only the rows you need or exclude the ones you don’t want.
Your database system applies the search condition to each row in the logical table defined by the WHERE clause.
A search condition contains one or more predicates, each of which is an expression that tests one or more value expressions and returns a true, false, or unknown answer.

❖ Note All the Database Schemas and Tables mentioned here, I will put link to it at the end of the Article.

How to Think Using WHERE Clause

First, look for any words or phrases in the given problem statement that indicate or imply some type of restriction.
Dead giveaways are the words “where”, “who” and “for” Here are some examples of the types of phrases you’re trying to identify:

“. . . who live in Bellevue.”
“. . . for everyone whose ZIP Code is 98125.”
“. . . who placed orders in May.”
“. . . for suppliers in California.”
“. . . who were hired on May 16, 1985.”
“. . . where the area code is 425.”
“. . . for Mike Hernandez.”
Second, When you find such a restriction, Study the phrase, and try to determine:
1. Which column is going to be tested.
2. What value that column is going to be tested against.
3. How the column is going to be tested. The answers to these questions will help you formulate the search condition for your WHERE clause.

Example:

Problem statement: “What are the names of our customers who live in the state of Washington?”
Translation: Select first name and last name from the customers table for those customers who live in Washington State.

By applying these questions to your translation statement:
- Which column is going to be tested? State
- What value is it going to be tested against? 'WA'
- How is the column going to be tested? Using the “equal to” operator

SQL:

SELECT CustFirstName, CustLastName  
FROM Customers  
WHERE CustState = 'WA';

Defining Search Conditions

Now that you have an idea of how to create a simple WHERE clause, let’s take a closer look at the five basic types of predicates you can define.

1. Comparison

The most common type of condition is one that uses a comparison predicate to compare two value expressions to each other.
As you can see in Figure 6-2 below, you can define six different types of comparisons using the following comparison predicate operators:

Equality and Inequality

Example #1:
- Problem statement: “Show me the first and last names of all the agents who were hired on March 14, 1977.”
- Translation: Select first name and last name from the agents table for all agents hired on March 14, 1977.
- SQL:
```
SELECT AgtFirstName, AgtLastName
FROM Agents
WHERE DateHired = '1977-03-14';
```
Example #2:
- Problem statement: “Give me a list of vendor names and phone numbers for all our vendors, with the exception of those here in Bellevue.”
- Translation: Select vendor name and phone number from the vendors table for all vendors except those based in ‘Bellevue’.
- SQL:
```
SELECT VendName, VendPhone
FROM Vendors
WHERE VendCity <> 'Bellevue';
```

❖ Note The SQL Standard uses the <> symbol for the “not equal to” operator. Several RDBMS programs provide alternate notations, such as != (supported by Microsoft SQL Server and Sybase) and ¬= ( supported by IBM’s DB2). <> and != (Both supported by MySQL and PostgreSQL ), Be sure to check your database system’s documentation for the appropriate notation of this operator.

Less Than and Greater Than

Example #1:
- Problem statement: “Are there any classes that earn more than four credits?”
- Translation: Select class ID from the classes table for all classes that earn more than four credits.
- SQL:
```
SELECT ClassID
FROM Classes
WHERE Credits > 4;
```
Example #2:
- Problem statement: “I need the names of everyone we’ve hired since January 1, 1989.”
- Translation: Select first name and last name as EmployeeName from the employees table for all employees hired since January 1, 1989.
- SQL:
```
SELECT FirstName || ' ' || LastName
AS EmployeeName
FROM Employees
WHERE DateHired >= '1989-01-01';
```

❖ Note The SQL Standard uses the || symbol for the “String Concatenation”. It is supported in Oracle and IBM’s DB2, But Several RDBMS programs provide alternate notations, CONCAT(string1, string2, ...., string_n).
This equivalent to:
SELECT CONCAT(FirstName, ' ',  LastName)
AS EmployeeName
FROM Employees
WHERE DateHired >= '1989-01-01';

2. Range

The second type of condition is one can test a value against a specific range of value with a range condition. Figure 6-3 below shows the syntax for this condition.

Example #1:

Problem statement: “Which staff members were hired in July 1986?”
Translation: Select first name and last name from the staff table where the date hired is between July 1, 1986, and July 31, 1986.

SQL:

SELECT FirstName, LastName
FROM Staff
WHERE DateHired
BETWEEN '1986-07-01' AND '1986-07-31';

This equivalent to:

SELECT FirstName, LastName
FROM Staff
WHERE DateHired >= '1986-07-01' AND  DateHired  <= '1986-07-31';

❖ Note That In BETWEEN ... AND, The left hand of AND can not be greater than the right one

Example #2:

Problem statement: “Show me all engagements that are scheduled to occur on October 10, 2017.”
Translation: Select engagement number, start date, and end date from the engagements table for engagements where October 10, 2017, is between the start date and the end date.

SQL:

SELECT EngagementNumber, StartDate, EndDate
FROM Engagements
WHERE '2017-10-10' BETWEEN StartDate AND EndDate;

3. Set Membership

The third type of condition is one can test a value against a list of explicitly defined values. Figure 6-4 below, uses IN predicate to determine whether the value of the first value expression matches any value within a finite list of other values.

Example #1:

Problem statement: “Which entertainers do we represent in Seattle, Redmond, and Bothell?”
Translation: Select stage name from the entertainers table for all entertainers based in Seattle, Redmond, or Bothell

SQL:

SELECT EntStageName
FROM Entertainers
WHERE EntCity
IN ('Seattle', 'Redmond', 'Bothell');

This equivalent to:

SELECT EntStageName
FROM Entertainers
WHERE EntCity = 'Seattle' OR EntCity = 'Redmond' OR EntCity = 'Bothell';

4. Pattern Match

The fourth type of condition is The pattern match condition, useful when you need to find values that are similar to a given pattern string or when you have only a partial piece of information to use as a search criterion. Figure 6-5 below shows the syntax for this type of condition.
A pattern string can consist of any logical combination of regular string characters and two special wildcard characters: the percent sign (%) and the underscore (_). (%) represents zero or more arbitrary regular characters, and (_) represents a single arbitrary regular character.

Here are a few examples of the types of phrases you’re likely to encounter:

“. . . begin with ‘Her’.”
“. . . start with ‘Ba’.”
“. . . include the word ‘Park’.”
“. . . contain the letters ‘han’.”
“. . . have ‘ave’ in the middle of it.”
“. . . with ‘son’ at the end.”
“. . . ending in ‘ez’.”

Example #1:

Problem statement: “Give me a list of customers whose last names begin with ‘Mar’.”
Translation: Select last name and first name from the customers table where the last name begins with ‘Mar’

SQL:

SELECT CustLastName, CustFirstName
FROM Customers
WHERE CustLastName LIKE 'Mar%';

Example #2:

Problem statement: “Show me a list of vendor names where the word ‘Forest’ appears in the street address.”
Translation: Select vendor name from the vendors table where the street address contains the word ‘Forest’

SQL:

SELECT VendName
FROM Vendors
WHERE VendStreetAddress LIKE '%Forest%';

You may encounter a search pattern request where the wildcards % and _ is a part of the pattern string. Introducing ESCAPE option:
Example #3:
- Problem statement: “Show me a list of products that have product codes beginning with ‘G_00’ and ending in a single number or letter.”
- Translation: Select product name and product code from the products table where the product code begins with ‘G_00’ and ends in a single number or letter
- SQL:
```
SELECT ProductName, ProductCode
FROM Products
WHERE ProductCode LIKE 'G\_00_' ESCAPE '\';
```
  Explaination:
  It’s clear that you need to use the ESCAPE option to answer this request—otherwise, the DBMS interprets the underscore character in the pattern string as a wildcard character.
  If I ask for LIKE 'G00', the database system will return rows where the product code has a ‘G’ for the first letter, any character in the second position, zeros in the third and fourth positions, and any character in the fifth position.
  When I define “\” as the escape character, the database system ignores the escape character but interprets the first underscore character literally, not as a wildcard.
  Because I did not use the escape character just before the second underscore, the database system interprets the second underscore as a true wildcard character.

5. Check for Null

The fifth type of condition is checking for null condition, you know that a NULL represents a missing or unknown value. This condition takes the value of the value expression and determines whether it is Null using the IS NULL predicate. Figure 6-6 below shows the syntax for this type of condition.

Example #1:

Problem statement: “Give me a list of customers who didn’t specify what county they live in.”
Translation: Select first name and last name as Customer from the customers table where the county name is unspecified

SQL:

SELECT CustFirstName || ' ' || CustLastName
AS Customer
FROM Customers
WHERE CustCounty IS NULL;

Excluding Rows with NOT

Up to this point, We’ve shown how to include specific rows in a result set. Let’s now take a look at how you exclude rows from a result set by using the NOT operator.
As you can see in Figure 6-7 below, this operator is an optional component of the BETWEEN, IN, LIKE, and IS NULL predicates.
A SELECT statement will ignore any rows that meet the condition expressed by any of these predicates when you include the NOT operator. The rows that will be in the result set instead are those that did not meet the condition.

Example #1:

Problem statement: “Show me a list of all the orders we’ve taken, except for those posted in July.”
Translation: Select order ID and order date from the orders table where the order date does not fall between October 1, 2017, and October 31, 2017

SQL:

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderDate NOT BETWEEN '2017-10-01'
AND '2017-10-31';

Example #2:

Problem statement: “I need the identification numbers of all faculty members who are not professors or associate professors.”
Translation: Select staff ID and title from the faculty table where the title is not ‘professor’ or ‘associate professor’

SQL:

SELECT StaffID, Title
FROM Faculty
WHERE Title
NOT IN ('Professor', 'Associate Professor');

Using Multiple Conditions

The requests We’ve worked with up to this point have been simple and have required only a single condition to answer. Now We’ll look at how you can answer complex requests using multiple conditions.
You can combine two or more conditions by using the AND and OR operators, and the complete set of conditions you’ve combined to answer a given request constitutes a single search condition. As Figure 6-8 below shows.
You can also combine a complete search condition with other conditions by enclosing the search condition in parentheses.

1. Using AND

The first way you can combine two or more conditions is by using the AND operator. You use this operator when all the conditions you combine must be met in order for a row to be included in a result set.

Example #1:

Problem statement: “Give me the first and last names of customers who live in Seattle and whose last names start with the letter ‘H’.”
Translation: Select first name and last name from the customers table where the city is ‘Seattle’ and the last name begins with ‘H’

SQL:

SELECT CustFirstName, CustLastName
FROM Customers
WHERE CustCity = 'Seattle'
AND CustLastName LIKE 'H%';

2. Using OR

The second way to combine two or more conditions is by using the OR operator. You use this operator when either of the conditions you combine can be met for a row to be included in a result set.

Example #1:

Problem statement: “I need the name, city, and state of every staff member who lives in Seattle or is from the state of Oregon.”
Translation: Select first name, last name, city, and state from the staff table where the city is ‘Seattle’ or the state is ‘OR’

SQL:

SELECT StfFirstName, StfLastName, StfCity, StfState
FROM Staff
WHERE StfCity = 'Seattle' OR StfState = 'OR';

2. Using AND and OR Together

You can use both AND and OR to answer particularly tricky requests that has multiple conditions.

Example #1:

Problem statement: “I need to see the names of staff members who have a 425 area code and a phone number that begins with 555, along with anyone who was hired between October 1 and December 31 of 2007.”
Translation: Select first name, last name, area code, phone number, and date hired from the staff table where the area code is 425 and the phone number begins with 555 or the date hired falls between October 1, 2017, and December 31, 2017
SQL:
```
SELECT StfFirstName, StfLastName, StfAreaCode,
StfPhoneNumber, DateHired
FROM Staff
WHERE (StfAreaCode = '425'
AND StfPhoneNumber LIKE '555%')
OR DateHired
BETWEEN '2017-10-01' AND '2017-12-31';
```
❖ Note, As you see comparison and pattern match conditions with an AND are dependent on each other and then treat them as a single unit, When you treat a combined set of conditions as a single unit you should enclose it in parentheses, so I probably could have gotten away with not placing parentheses around the two comparisons linked with AND. Always use parentheses to make it crystal clear

Example #2:

Problem statement: “I need the name and title of every professor or associate professor who was hired on May 16, 1989.”
Translation: Select first name, last name, title, and date hired from the staff table where the title is ‘professor’ or ‘associate professor’ and the date hired is May 16, 1989

SQL:

SELECT StfFirstName, StfLastName, Title, DateHired
FROM Staff
WHERE (Title = 'Professor'
OR Title = 'Associate Professor')
AND DateHired = '1989-05-16';

Order of Precedence

The SQL Standard specifies how a database system should evaluate single conditions within a search condition and the order in which those evaluations take place.
By default, the database evaluates conditions from left to right. This is particularly true in the case of simple conditions.
When a search condition contains various types of single conditions, the database evaluates them in a specific order based on the operator used in each condition. The SQL Standard defines the following order of precedence for operator evaluation.

Evaluation Order	Type of Operator
1	Positive sign (+), negative sign (−)
2	Multiplication (*), division (/)
3	Addition (+), subtraction (−)
4	=, <>, <, >, <=, >=, BETWEEN, IN, LIKE, IS NULL
5	NOT
6	AND
7	OR

Prioritize and Avoid Ambiguous Conditions

You can greatly increase the accuracy of your search conditions by understanding the order of precedence. This knowledge will help you formulate exactly the right condition for the request at hand. But you must be careful to avoid defining ambiguous conditions because they can produce unexpected results.

Let’s use the following example to take a look at this potential problem:

SELECT CustFirstName, CustLastName, CustState,
CustZipCode
FROM Orders
WHERE CustLastName = 'Patterson'
AND CustState = 'CA'
OR CustZipCode LIKE '%9';

In this instance, it’s difficult to determine the true intent of the search condition because there are two ways you can interpret it.
1. You’re looking for everyone named Patterson in the state of California or anyone with a ZIP Code that ends with a 9.
2. You’re specifically looking for everyone named Patterson and anyone who lives in California or has a ZIP Code that ends with a 9.
If you have memorized the evaluation order table, you know that the first way is correct because your system should evaluate AND before OR. But are you always going to remember the evaluation sequence?
You can avoid this ambiguity and make the search condition clearer by using parentheses to combine and prioritize certain conditions.
1. To follow the first interpretation of the search condition, you define the WHERE clause in this manner:
```
WHERE (CustLastName = 'Patterson' AND CustState = 'CA') 
OR CustZipCode LIKE '%9'
```
2. The second interpretation:
```
WHERE CustLastName = 'Patterson' AND (CustState = 'CA' 
OR CustZipCode LIKE '%9')
```

Additional Resources and materials

Sample Databases Used in This Book Implemented in various DBMS like MySQL, PostgreSQL, MS SQL, MS Access with the sample data and schema diagrams. Download It

SQL Queries For Mere Mortals: Filtering Your Data

Filtering Your Data by Writing Predicates in Where Clause

Table of contents

Refining What You See Using WHERE

The WHERE Clause

How to Think Using WHERE Clause

Defining Search Conditions

1. Comparison

Equality and Inequality

Less Than and Greater Than

2. Range

3. Set Membership

4. Pattern Match

5. Check for Null

Excluding Rows with NOT

Using Multiple Conditions

1. Using AND

2. Using OR

2. Using AND and OR Together

Order of Precedence

Prioritize and Avoid Ambiguous Conditions

Additional Resources and materials