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
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).
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'
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'
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 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.
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:
- Write a query to pull all Female customers with a Bachelors degree.
- Write a query to pull customers with an income between 50,000 and 60,000.
- Write a query to pull all customers who have the same birthday as you.
The next article will cover some more advanced applications of WHERE statements.