Filtering Joined Tables with WHERE

Hello! Welcome to the final lesson in our module on connecting product data.

Introduction

In our last session, we explored how to use LEFT JOIN to find not only where relationships exist between tables but also where they don't—a powerful technique for identifying users who haven't performed a specific action. We saw that JOIN operations create a temporary, combined table from multiple sources.

Today, we'll learn how to apply the WHERE clause to this combined table. You're already familiar with using WHERE to filter a single table, but applying it to a JOIN result unlocks a much deeper level of analysis.

This lesson directly addresses the learning outcome: Apply the WHERE clause to filter the results of a joined table. By the end of this session, you'll be able to ask highly specific, multi-table questions, such as "Show me all the premium feature events triggered by users who signed up from our latest marketing campaign." This is a fundamental skill for effective user segmentation.

1. The Basic Principle: Join First, Filter Second

The most important concept to grasp is the logical order of operations in a SQL query:

1. FROM / JOIN: The database first performs the join, creating a new, temporary table in memory that combines columns from all the tables you've specified.
2. WHERE: The WHERE clause is then applied to this new, temporary table, filtering out rows that don't meet your conditions.
3. SELECT: Finally, the SELECT statement picks the columns you want to see from the remaining rows.

Think of it like a two-step process in a design tool: first, you group several objects together (JOIN), and then you apply a filter or style to that entire group (WHERE).

Let's start with a resource that provides a clear definition and a simple, practical example.

2. Filtering INNER JOIN Results

Let's apply this to a common product scenario. Imagine we have a users table and an events table. We want to find all events performed by users who signed up as part of a specific campaign.

Here’s how we would structure the query:

SELECT
    u.email,
    e.event_name,
    e.event_timestamp
FROM
    users AS u
INNER JOIN
    events AS e ON u.user_id = e.user_id
WHERE
    u.signup_campaign = 'Summer_Promo_2024';

In this query:

1. INNER JOIN creates a large set of rows, matching every user with every event they have ever performed.
2. WHERE u.signup_campaign = 'Summer_Promo_2024' then filters that large set, keeping only the rows where the user came from the 'Summer_Promo_2024' campaign.

The following video provides an excellent visual walkthrough of this process. Although the example is about chemical compounds, the logic is identical.

3. A Critical Distinction: WHERE vs. ON with LEFT JOIN

In the previous lesson, you learned to use WHERE ... IS NULL with a LEFT JOIN. But what happens when you use other conditions? The placement of your filter—in the ON clause or the WHERE clause—becomes critically important with LEFT JOIN.

• Condition in WHERE: Filters the final result set after the join. If you filter on a column from the right table (e.g., WHERE events.event_name = 'button_click'), you implicitly discard all the rows where that column is NULL. This effectively turns your LEFT JOIN into an INNER JOIN, because you lose all the users who didn't perform that event.

• Condition in ON: Filters the right table before the join is performed. This is useful when you want to keep all rows from the left table but only join them to a specific subset of rows from the right table.

Let's illustrate with a product question: "Show me all users, and for each user, list only their 'add_to_cart' events."

Incorrect Approach (using WHERE):

SELECT u.user_id, e.event_name
FROM users AS u
LEFT JOIN events AS e ON u.user_id = e.user_id
WHERE e.event_name = 'add_to_cart';

This query will only return users who have 'add_to_cart' events. It fails to show users who have never added anything to their cart.

Correct Approach (using ON):

SELECT u.user_id, e.event_name
FROM users AS u
LEFT JOIN events AS e ON u.user_id = e.user_id AND e.event_name = 'add_to_cart';

This query returns all users. For users who have 'add_to_cart' events, it shows them. For all other users, e.event_name will be NULL.

This is a subtle but powerful difference. The following resource provides an excellent, detailed example that breaks this down.

Rule of Thumb:

• Conditions that link the tables go in the ON clause.
• Conditions that filter the final, combined result go in the WHERE clause.

4. Practice Exercise

Let's put this all together with a scenario you might encounter as a product designer.

Scenario:
You work at a project management tool company. You want to understand how users who signed up through a specific partner (PartnerA) are using the "task_completed" feature.

You have two tables:
users table:

events table:

Your Task:
Write a SQL query to find the email addresses of users who signed up via PartnerA and have performed a task_completed event.

Think about the steps:

1. Which tables do you need to join?
2. What kind of join is appropriate? (INNER or LEFT?)
3. What are your filtering conditions?

SELECT
    u.email
FROM
    users AS u
INNER JOIN
    events AS e ON u.user_id = e.user_id
WHERE
    u.signup_source = 'PartnerA' AND e.event_name = 'task_completed';

Conclusion

Congratulations on completing the "Connecting Product Data with Joins" module! You've built a solid foundation, moving from querying single tables to combining multiple data sources and filtering them to get precise answers.

Key Takeaways:

• The WHERE clause filters the rows of the temporary table created by a JOIN.
• The logical order of operations is JOIN -> WHERE -> SELECT.
• For INNER JOIN, it's best practice to keep join logic in ON and filter logic in WHERE.
• For LEFT JOIN, the placement of a filter is critical:
  • In WHERE, it filters the final result.
  • In ON, it filters the right table before the join.

Preview of the Next Lesson

You now have the skills to select, join, and filter data to get specific lists of users, events, and other entities. The next step in your data journey is to move from retrieving lists to calculating metrics.

In our next module, "Calculating Core Product Metrics," we will start with the COUNT() function. This will enable you to answer questions like, "How many users signed up last week?" and "How many 'add to cart' events happened yesterday?" This is the gateway to building dashboards and tracking your product's key performance indicators (KPIs).