In today’s lesson you’re going to learn how filter the results returned from your queries using the WHERE clause. This clause is important as only those records matching the where clause’s conditions are returned in the query results. The objectives of today’s lesson are to:
- Learn about various condition types, such as Equality, Range, and Membership
- Comprehensive example with Select, Where, and Order By
- Successfully complete the exercises
The WHERE Clause
A where clause is a condition that must be me in order for row to be returned in a query. A simple example of a Select statement with a where clause is Select CustomerName from Customers Where State=’MI’ In this example only customer in the state or Michigan are selected. In order for a row to be returned the expression State=’MI’ must be true, that is State must equal ‘MI’ In general a row is included in a result when the where clause condition is true. The condition of a where clause can be made up of one or more tests. Through the use of Boolean logic you can combine these tests to for complex comparison between one or more columns or expressions. In this lesson we’ll cover three type of conditions: Equality, Range and Membership.
Equality Conditions
An equality condition test that a column or expression matches a value. As you may expect, the equals sign is used to form an equality. It the following SQL statement were returning all customers contacts that are owners.
SELECT CompanyName, ContactName FROM Customers WHERE ContactTitle = 'Owner';
Other examples of equality conditions include:
- Quantity = 30
- UnitPrice = .25
- City = ‘El Paso’
You may have noticed that number are written as is, however text is enclosed in single quotes. You can also incorporate expressions into a equality condition. For example if your data was inconsistent and states in the database in various capitalization combination like “mi”, “MI”, and “mI,” which are all different values as far as an equality is concerned, you could compare the uppercase version to be safe. To do so your condition would be
Upper(State) = 'MI'
That way regardless, of however your state was capitalized, it would always evaluate to all caps and then safely compared to the proper abbreviation of the state.
Inequalities
Sometime you need to exclude a value. To do this you can use an inequality. The operator for this is <>. Example of inequalities are:
- State <> ‘MI’
- Quantity <> 0
If you were to return all customer contact who weren’t owners of the company, then your query would be
SELECT CompanyName, ContactName FROM Customers WHERE ContactTitle <> 'Owner';
Tip! I remember that <> stands for not equals by seeing that the < and > oppose each other.
Range Conditions
Sometime you want to know when a value falls within a range, such as quantities greater than 10. The ranges conditions we are most interested in are Greater Than, Less Than, and Between. The operators to perform the various test are:
- > (greater than)
- >= (greater than or equal to)
- < (less than)
- <= (less than or equal to)
- BETWEEN
Consider our OrderDetails table. IF you want to know order where the order quantity is more than 10, then
Quantity > 10
Is the proper test. The range conditions work on text just as well numbers. The condition State < ‘NE’ return all state abbreviation prior to Nebraska’s To include Nebraska you would write
Sate <= 'NE'
Thus to find all customer within states less than or equal to Nebraska you would write
SELECT CompanyName, ContactName FROM Customers WHERE State <= 'NE';
You can also use expressions. To find all OrderDetails with a TotalPrice more than 100 dollars, your query would look like
SELECT OrderID, ProductID ,UnitPrice, Quantity ,UnitPrice * Quantity as TotalPrice FROM OrderDetails Where TotalPrice >= 100;
But what if you want to TotalPrices that fall within 100 and 200 dollars? How can you pull this one off? Where there are actually a couple of ways, but the one we’ll talk about now is the Between operator. The between operator is used in a condition as
TotalPrice BETWEEN 100 and 200
As you probably suspect, you can also use ti for test as well. If you’re looking for all the states between a and c you would
States BETWEEN 'A' and 'CZ'
Back to our TotalPrice example the full statement for this queries is
SELECT OrderID, ProductID, Quantity * UnitPrice as TotalPrice FROM OrderDetails WHERE TotalPrice BETWEEN 100 and 200;
Note that the BETWEEN operator is inclusive, that is it includes both the beginning and ending value.
Membership Conditions
The last condition type to learn about in this lesson is the membership type. The membership type allow you to conduct multiple match tests compactly in one statement. For instance, consider if you have a couple spelling variation for the leader of the company such as ‘Owner’, ‘President’, and ‘CEO.’ In c case like this you could use the in operator to find all matches
ContactTitle IN ('CEO', 'Owner', 'President')
The above will math or return turn if the contact title is either ‘CEO’, ‘Owner’, or ‘President.’ To use the IN comparison operator separate the items you which to test for with commas and be sure to enclose them in parenthesis The full SQL statement for our example is
SELECT CompanyName, ContactName, ContactTitle
FROM Customers
WHERE ContactTitle IN ('CEO', 'Owner', 'President');
Comprehensive Example
No lets hook it all together with an example to both filter and sort a query. The overall structure for the select statement is
SELECT columns FROM Table WHERE WhereClause ORDER BY Columns
In the prior section the query to select contacts who were owners is:
SELECT CompanyName, ContactName, ContactTitle, State
FROM Customers
WHERE State IN ('FL', 'TX');
To sort by ContactName just add an ORDER BY clause as
SELECT CompanyName, ContactName, ContactTitle
FROM Customers
WHERE State IN ('FL', 'TX')
ORDER BY ContactName;
Exercises
It’s important to practice! Use the sample database to answer these questions.
- Select OrderID, OrderDate, and FreightCharge for all Orders whose freight charges are less than $10.00
- Select OrderID, OrderDate, and FreightCharge for all Orders whose freight charges are within the $4.00 to $7.00 range
- Find all employees whose first name is “James”
- Find all productIDs from these orders 1, 4, 10, 11
Answers to Exercises
Congratulations! You just learned how to filter query results using the where clause. More tutorials are to follow! Remember! I want to remind you all that if you have other questions you want answered, then post a comment or tweet me. I’m here to help you. What other topics would you like to know more about?
