SQL Basics: The WHERE Clause

Working with the WHERE clause and SQL operators to refine data requests.

In my last post I covered the basics of the SELECT statement and what exactly Structured Query Language (SQL) is. In today’s post, we’re adding a new clause to the SELECT statement that will allow us to constrain our data requests. If this is your first introduction to SQL, I recommend you check out my previous post which you can find here.

The WHERE Clause

Oh WHERE, oh WHERE can my data be? - Eddie Vedder, Pearl Jam lead singer and SQL enthusiast.

Okay so Eddie Vedder probably isn’t an SQL enthusiast, but if he were, I’d bet he would be a big fan of the WHERE clause.

Put simply, the WHERE clause is used to filter rows in a table. When we want to limit the number of rows returned based on a condition or set of conditions, we use the WHERE clause.

Remember our BREWERY table from last time?

SELECT *
FROM BREWERY;

Let’s say we’re planning a trip to Asheville, NC, a town known for its eccentric style and thriving beer scene. Before we drive up into the mountains to sample some brews, we would like to know what breweries we can potentially visit in NC. Now we could use SELECT * FROM BREWERY and scroll through our table searching for breweries where the value for the BREWERY_STATE attribute is TN or we could use the WHERE clause.

SELECT *
FROM BREWERY
WHERE BREWERY_STATE = 'TN';

Maybe instead of looking at a list of all of the breweries in NC, we want to look up a specific brewery we heard about to check and see if it’s located in NC.

SELECT *
FROM BREWERY
WHERE BREWERY_NAME = 'GREEN MAN BREWERY';

Be sure to pay special attention to the case of your string conditions inside your WHERE clause. Had we tried to use WHERE BREWERY_NAME = 'Green Man Brewery'; our return would have been empty because in the BREWERY table, “GREEN MAN BREWERY” is in all caps.

SELECT BREWERY_ID, BREWERY_NAME, BREWERY_STATE, BREWERY_LICENSE
FROM BREWERY
WHERE LOWER(BREWERY_NAME) = 'green man brewery';

Because we only included the text manipulation function LOWER in the where clause and not in the SELECT clause, the “GREEN MAN BREWERY” was returned in its default case instead of lower case.

If we have any uncertainty surrounding case when searching inside a particular table, most DBMS have text manipulation functions such as UPPER and LOWER that we can use to temporarily convert text to one case or another.

Conditional Operators

Sometimes we don’t have an exact value we’re looking for. In those search cases we need to use conditional operators. They allow us to expand our search criteria beyond exact values.

Here are some of the most common conditional operators you’ll encounter.

If we wanted to find brewery IDs where the BREWERY_ID is between 1002 and 1005, we would write our query like this:

SELECT BREWERY_ID
FROM BREWERY
WHERE BREWERY_ID BETWEEN 1002 AND 1005;

The WHERE clause in this example actually uses two operators. The AND in this statement is actually an operator itself.

Logical Operators

Logic operators allow you to combine more than one search condition at a time. For instance if we want to limit our results for breweries where the BREWERY_ID is 1005 and the state is NC, our query would use the AND logic operator. If we wanted to return a list of breweries located in NC or TN we would use the OR operator. The nice thing about logic operators in SQL is that they’re pretty intuitive.

For practice let’s write a query with multiple conditions. Let’s say we want to look at breweries in either NC or TN where the BREWERY_ID is greater than 1005.

SELECT *
FROM BREWERY
WHERE BREWERY_STATE = 'NC' OR BREWERY_STATE = 'TN' AND BREWERY_ID > 1005;

Hmm…

Was this the table you were expecting?

Looks like we’ve got NC breweries where the IDs is below 1005 even though we requested only IDs above 1005. Let’s look closely at what’s actually going on here.

The order in which the operators are evaluated is causing us trouble. In most DBMS environments, the AND operator is executed first. This means that in our example above, any breweries located in TN and with a BREWERY_ID > 1005 are returned, and any breweries located in NC regardless of their ID.

To fix this problem we just need to be more specific. By adding parentheses around our conditions, we can control the order in which they’re evaluated. Think of your old TI-83 calculator days.

SELECT *
FROM BREWERY
WHERE (BREWERY_STATE = 'NC' OR BREWERY_STATE = 'TN') AND BREWERY_ID > 1005;

To Sum It Up

The WHERE clause should be used whenever you need to constrain your data. Conditional operators can help you further refine your queries beyond exact matches, and if you find you need multiple conditions, include logic operators such as AND and OR.

In the next post of the SQL BASICS series, we’ll continue expanding our SQL toolkit. I’ll go into how grouping rows using the GROUP BY clause can be handy and how to sort our returned rows using the ORDER BY clause.