Welcome back, travelers! The journey continues in learning SQL. In case you missed it, the past couple of posts were learning to use SELECT DISTINCT and Restoring an SQL database with table schema only.

Today we’re going to learn about using the SELECT clause with the WHERE statement for essential SQL queries.

A Quick Recap, or, Why SELECT WHERE is Important

As previously mentioned, we’ve covered a range of beginning SQL topics. Mainly, we’ve learned about using the SELECT statement to query all (*) or query specific columns of data from a table. Because we’ve been working with a pared down table with only a few hundred rows, it’s not a problem in this “academic” setting to return all the rows. But what if we’re working in a larger database? A recent keyword research dataset 75,000 rows long comes to mind. (Though I would imagine that too, would be a small dataset in the grand scheme of things, but I digress.)

When we start working with larger databases, granularity will become vitally important. This is where (pun not intended) the SELECT WHERE statement comes in.

Sample Syntax of the SELECT WHERE Statement

With proof of concept in mind, let’s jump into it headlong. Below is a syntax example, demonstrating what a SELECT WHERE SQL query might look like.

SELECT column_1,column_2

                FROM table_name

                WHERE condition1;

The SELECT statement is old news to you adventurers that have been following along. The WHERE portion of this query will be the power in this article.

More About the SELECT WHERE SQL Statement

The WHERE clause appears right after the FROM clause of the SELECT statement. The conditions listed within the WHERE clause are used to filter the rows returned from the SELECT statement. Because we’re working in pgAdmin / PostgreSQL, we’ll have available standard operators to construct the conditions. Better still, some (or most) of the operators we’ll look at are fairly universal, so these operators should work in MySQL, Microsoft SQL, Amazon Redshift, etc.

List of SELECT WHERE Operators

Below is a list of common SELECT WHERE operators. Again, most of these should be fairly universal, regardless of the SQL database management program you’re using.

Plugging this back into SQL statements, we’ll be using the operators on the left to filter down and return only specific rows in our queries.

Sample SELECT WHERE Statements

Let’s  cover some guiding examples that will help us apply the SELECT WHERE operators. To start, we’ll kill two birds with one stone: jogging the memory by utilizing a previous query and exploring the table we’ll be querying. Below, we’ll be a bit naughty by calling on all columns from the table.

SELECT * FROM customer;

Above, we’ll see our results and some candidate columns to query the heck out of. Let’s keep moving.

Example 1: The Basic SELECT WHERE Statement with 1 Condition, Returning Only Customers with a Certain Name

Okay, let’s say that we want to only return customers of a certain name, say, “James”. The SELECT WHERE statement will help us make quick work of this database need.

SELECT last_name,first_name

                FROM customer

                WHERE first_name = ‘James’;

If all goes correctly, we should only get back a customer with the first name James. ‘Ello James!

Side note 1: you don’t have to return the columns you’re filtering against. For example, we could return the email column and still filter by name.

Example 2: A SELECT WHERE Statement Using 2 Conditions, Returning Customers with a Certain First AND Last Name

Perhaps you’ll want to do something more targeted with your data. I know this is a narrow and frankly creepy example of calling out one name, but think maybe of a City/State or Source/Medium pairing? Anyway, with the sample dataset we have, below we use the AND logical operator to combine two conditions into one query.

SELECT last_name,first_name

                FROM customer

                WHERE first_name = ‘Jared’ AND last_name = ‘Ely’;

If executed properly against this particular sample dataset, we should be returned only the values for one fictional Mr. Jared Ely.

Quick side note 2: that we should have mentioned sooner: we’re using single quotes here because the values we’re querying against are string values. As such, the single quotes help us match format, et cetera.

Example 3: Another SELECT WHERE Statement Using 2 Conditions, Returning Customer ID’s where payment was in certain dollar amount ranges

Let’s say we are trying to identify a range of customers in our database. In this third example, we want to query Customer ID’s and names in a certain range. We could think of it as a feeble attempt to get our first customers or our most recent customers for a special email flight. Below, we’ll exercise the OR operator to accomplish our desired output.

SELECT customer_id,first_name,last_name

                FROM customer

                WHERE customer_id <= 2 OR customer_id >=20 ;

Understanding the Subtle Differences Between AND / OR

Quick note about differences between the AND / OR operators. If you’re trying to filter data from two different columns, then AND is your filter. If you’re trying to get distinct values within a single column, then the OR operator will be best suited for the job.

Wrap-Up

What a world of possibilities we’ve opened! I found myself needing to slow down and pay more attention to detail in this area of learning. The nuances of selecting certain columns but filtering by others when practicing threw me for a loop once or twice.

Thanks for joining, in the next article, I’ll be covering some introductory material around the COUNT function. In the meantime, check  out our running list of posts on how to learn SQL.

Zach