Learning SQL, Part 3 - Where Statements

This is the third article in the learning SQL series:

• Part 1: Setup your environment
• Part 2: Basic Select Statements
• Part 3: Where Statements (this article)
• Part 4: More Where Statements
• Part 5: Aggregations and Group By Statements
• Part 6: Joins

Filtering in SQL: The WHERE Statement

Previous articles reviewed how to setup your database environment and selecting records. In practice however, you’re typically interested in analyzing a subset of specific records. This is where the SQL WHERE statement comes in. The keyword is located towards the end of your SQL query after your FROM statement and any other references to table names (such as joins, which will be covered in a later article).

Simple Text Column Filters

Continuing on the example from prior articles in the series - analyzing the DimCustomer table in our AdventureWorks database, let’s say we want to filter to customers with the first name of Bob. In the query below, note the WHERE keyword added after the table name on the following row. The WHERE keyword signifies the start of any filter conditions. In this example, one condition is added where the FirstName column equals Bob. Text strings are enclosed in single parentheses.

SELECT *
FROM [AdventureWorksDW2017].[dbo].[DimCustomer]
WHERE
	FirstName = 'Bob'

Multiple Conditions

You can filter by more than one column at a time. In the prior example, how would we just grab the Bob Chapman row? Multiple conditions in SQL are separated by the AND keyword. We simply filter the FirstName of Bob and LastName of Chapman.

SELECT *
FROM [AdventureWorksDW2017].[dbo].[DimCustomer]
WHERE
	FirstName = 'Bob'
AND LastName = 'Chapman'

Numeric Filters

For numeric columns, we can still use the equals sign, similar to prior examples. However, we can also use equality comparisons such as greater than or less than. If we want to target marketing efforts to empty nesters, we may write a query such as the following - searching for customers with > 0 children, but none living at home.

SELECT *
FROM [AdventureWorksDW2017].[dbo].[DimCustomer]
WHERE
	TotalChildren > 0
AND NumberChildrenAtHome = 0

Date Filters

Date columns have some similarities to both text strings and numbers. The exact syntax can vary by database technology, but I typically use ‘YYYY-MM-DD’ format for any date values. In the following example, the query pulls customers with a BirthDate after January 1st, 1970.

SELECT *
FROM [AdventureWorksDW2017].[dbo].[DimCustomer]
WHERE
	BirthDate > '1970-01-01'

You can use the equals operator, similar to the first example in this article, as well as the BETWEEN keyword to filter for a range of dates. For example, BirthDate BETWEEN ‘1970-01-01’ AND ‘1970-01-02’ would return records with BirthDates in the first 3 days of 1970.

Extra Practice

That’s it for this article. Filtering is very common in SQL queries, so you’ll get plenty of practice using these. Test out writing SQL queries on your own examples against this customer table to get the hang of it. Some ideas to try to get used to writing WHERE statements:

1. Write a query to pull all Female customers with a Bachelors degree.
2. Write a query to pull customers with an income between 50,000 and 60,000.
3. Write a query to pull all customers who have the same birthday as you.

More Advanced Where Examples

The next article will cover some more advanced applications of WHERE statements.

Next Steps

Check out additional articles in this series here:

• Part 1: Setup your environment
• Part 2: Basic Select Statements
• Part 3: Where Statements (this article)
• Part 4: More Where Statements
• Part 5: Aggregations and Group By Statements
• Part 6: Joins