Filtering Data with Comparison Operators

1. Introducing the WHERE Clause

The SELECT statement retrieves the columns you specify, but it returns all rows from the table by default. The WHERE clause is used to filter these rows and return only those that meet a specific condition.

The basic syntax is:

SELECT column_name(s)
FROM table_name
WHERE condition;

The condition is an expression that evaluates to true or false for each row. If the condition is true, the row is included in the result set.

Let's start with a quick video that introduces the concept.

2. Filtering by Exact Matches with =

The most straightforward way to filter is to look for an exact match using the equals (=) operator.

A critical rule in SQL is how you format the values in your condition:

• Text values must be enclosed in single quotes (e.g., 'premium').
• Numeric values are not enclosed in quotes (e.g., 100).

Let's use our hypothetical users table from the last lesson.

Example 1: Finding users on the 'premium' plan
Here, subscription_plan is a text column.

SELECT user_id, email, signup_date
FROM users
WHERE subscription_plan = 'premium';

This query will scan the users table and return the user_id, email, and signup_date only for the rows where the subscription_plan column contains the exact string 'premium'.

Example 2: Finding a specific user by ID
Here, user_id is a numeric column.

SELECT user_id, email, signup_date
FROM users
WHERE user_id = 123;

This query will return the details for the single user whose ID is 123.

To solidify this distinction, please read the following resource.

3. Using Comparison Operators

Filtering isn't limited to exact matches. You can perform comparisons on numerical data (and even on dates or text) using a set of standard operators. This is essential for analyzing user behavior, such as finding users with high activity levels or identifying recent signups.

The main comparison operators are:

Most SQL versions accept both != and <> for "not equal." != is generally more common and readable.

Let's watch a video that demonstrates how to use these operators with numerical data.

A Note on Comparing Text

While you can use operators like > or < on text columns, the behavior is based on alphabetical order. For example, WHERE first_name > 'M' would return names starting with N, O, P, and so on. This can be useful in some cases but is less common than numerical comparisons.

4. Practice: Analyzing User Activity

Let's apply what you've learned. Imagine you have access to a user_activity table that logs user actions within your product. The table has the following columns:

• event_id (numeric, unique ID for each event)
• user_id (numeric)
• event_name (text, e.g., 'login', 'viewed_dashboard', 'created_project')
• session_duration_minutes (numeric)

Write the queries for the following tasks.

Problem 1: Write a query to retrieve the user_id and session_duration_minutes for all 'login' events.

SELECT user_id, session_duration_minutes
FROM user_activity
WHERE event_name = 'login';

Problem 2: Write a query to find all events where the session lasted longer than 60 minutes. Return all columns.

SELECT *
FROM user_activity
WHERE session_duration_minutes > 60;

Problem 3: Write a query to find the user_id for all events that were not 'viewed_dashboard'.

SELECT user_id
FROM user_activity
WHERE event_name != 'viewed_dashboard';

Problem 4: You want to investigate very short sessions that might indicate a user is having trouble. Write a query to find the user_id and session_duration_minutes for all sessions that lasted 2 minutes or less.

SELECT user_id, session_duration_minutes
FROM user_activity
WHERE session_duration_minutes <= 2;

Conclusion

Fantastic work today! You've just learned one of the most fundamental skills in SQL: filtering data. By combining SELECT, FROM, and WHERE, you can now move beyond simply viewing a table to precisely extracting the information you need to answer specific questions.

Here are the key takeaways from this lesson:

• The WHERE clause is used to filter rows based on one or more conditions.
• Text values in a condition must be enclosed in single quotes ('...'), while numeric values are not.
• A range of comparison operators (=, !=, >, <, >=, <=) can be used to create powerful and flexible filters.

You can now isolate rows based on a single criterion. But what if you need to be more specific? For example, how would you find users who are on the 'premium' plan and who signed up in the last month?

In our next lesson, we will explore how to combine multiple conditions using the AND, OR, and NOT operators. This will dramatically expand your ability to define and analyze complex user segments.