Filtering Data with WHERE

Finding the Needle in the Haystack

The Power of Selection

Imagine you are looking for a specific book in a massive library. You wouldn't browse every single shelf; instead, you would head straight to the section for a specific author or genre. In SQL, the WHERE clause acts as your personal filter.

Imagine you are looking for a specific book in a massive library. You wouldn't browse every single shelf; instead, you would head straight to the section for a specific author or genre. In SQL, the WHERE clause acts as your personal filter, allowing you to bypass thousands of irrelevant rows and retrieve only the data that meets your exact criteria.

Our E-commerce Schema

Data Sources

We will use two primary tables: Customers and Orders. Understanding the columns available is the first step in writing a filter.

To filter data effectively, we first need to know what we're working with. For this lesson, we will focus on the Customers and Orders tables from our E-commerce database. Notice the columns like city in Customers and total_amount in Orders—these are the fields we will use to build our conditions.

The WHERE Clause Syntax

Syntax & Operators

The WHERE clause is placed directly after the FROM clause. It uses comparison operators to evaluate each row.

The WHERE clause is placed directly after the FROM clause. It uses comparison operators to evaluate each row: if the condition is true, the row is included in your results. If it is false, the row is discarded. The equal sign checks for an exact match. Greater than and less than are perfect for numeric thresholds. Not equal to allows you to exclude specific values.

Filtering Text vs. Numbers

The Quote Rule

How you write your filter depends on the data type:

There is one golden rule to remember: check your data types. To find all customers living in 'Chicago', we wrap the text value in single quotes. But to find high-value orders over 500, we use the numeric value without quotes. Putting quotes around a number can slow down your database or cause errors.

Common Pitfalls

Avoid These Mistakes

Filtering seems simple, but beginners often trip over these three areas:

Watch out for these common pitfalls. First, case sensitivity: in many databases, 'chicago' with a lowercase c is not the same as 'Chicago' with a capital C. Second, quoting numbers can lead to performance issues. Finally, the NULL trap: you cannot use the equal sign to find missing data; you must use 'IS NULL', which we'll explore in a later lesson.

Exercise 1: Find the Miamians

Practice Challenge

Write a query to find the first_name and email of all customers who live in 'Miami'.

Let's put your skills to the test. Write a query to find the first name and email of all customers who live in Miami. Remember to use single quotes for the city name.

Exercise 2: Exact Order Totals

Practice Challenge

Retrieve all columns from the Orders table where the total_amount is exactly 150.

Now, let's look for specific order amounts. Retrieve all columns from the Orders table where the total_amount is exactly 150. Pay close attention to whether you need quotes or not!

Exercise 3: Exclude the Cancelled

Final Challenge

Find all orders that do NOT have a status of 'Cancelled'. Use the != operator.

Final challenge. A business analyst needs to see all orders that haven't been cancelled. Use the 'not equal to' operator to filter out the 'Cancelled' status.