LEFT JOIN for User Actions

Hello! Welcome to your fourth lesson in the "Connecting Product Data with Joins" module.

Introduction

In our previous lessons, you learned how to use INNER JOIN to find relationships between tables, like linking users to the events they've triggered. You also mastered using table aliases (AS) to write cleaner, more professional queries.

However, INNER JOIN only shows you where a connection exists. As a product designer, some of the most critical insights come from understanding what doesn't happen. For example:

• Which users signed up but never created a project?
• Which users saw the new feature announcement but never clicked it?
• Which customers have items in their cart but have never completed a purchase?

Answering these questions is impossible with an INNER JOIN. Today, we'll learn the tool that makes it possible: the LEFT JOIN.

This lesson directly addresses the learning outcome: Use LEFT JOIN to find users who have or have not performed a certain action. By the end of this session, you'll be able to analyze both user engagement and disengagement, a crucial skill for identifying friction points and opportunities in your product.

1. Understanding the LEFT JOIN

An INNER JOIN looks for the intersection of two sets of data. A LEFT JOIN is different: it takes everything from the first table (the "left" table) and pairs it with any matching data from the second table (the "right" table).

If a row in the left table has no match in the right table, it still appears in the results. The columns from the right table are simply filled with NULL values for that row.

This diagram is a fantastic visual summary of different JOIN types. For now, focus on the top-left diagram for LEFT JOIN.

Let's watch a short video that walks through this concept.

As the video explained, the key is that all records from the left table (the one specified in the FROM clause) are preserved.

Here is another visual that reinforces this idea, joining a Customers table with an Orders table.

2. LEFT JOIN Syntax and a Practical Example

The syntax for a LEFT JOIN is very similar to the INNER JOIN you already know. You just replace INNER JOIN with LEFT JOIN.

SELECT
    u.user_id,
    u.email,
    p.project_name
FROM
    users AS u
LEFT JOIN
    projects AS p ON u.user_id = p.user_id;

In this query:

• users is the left table. We will get every single user back.
• projects is the right table. We will only get project data where the user_id matches a user.
• If a user has no projects, p.project_name will be NULL.

Let's look at a complete example with sample data.

3. Finding Users Who Have NOT Performed an Action

Now for the most powerful application of LEFT JOIN for product analysis: finding the absence of an action.

We can do this by combining a LEFT JOIN with a WHERE clause that filters for NULL values in the right table. If a column from the right table is NULL, it means no match was found.

The logic is:

1. LEFT JOIN the users table to an actions table (e.g., projects, events, purchases).
2. This gives you all users, with NULLs where they haven't performed the action.
3. Add WHERE actions_table.id IS NULL to filter the results down to only those users.

Look back at the SQL Joins Cheatsheet image. The diagram directly below the LEFT JOIN one shows this exact pattern. It isolates the part of circle A that does not overlap with B.

Let's see the query in action.

This LEFT JOIN ... WHERE ... IS NULL pattern is one of the most valuable tools in your SQL toolkit for user behavior analysis.

4. Practice Exercise

Let's apply this to a realistic product design scenario.

Scenario:
Your team recently launched a new "bookmarks" feature. You want to analyze its adoption. You have two tables: users and bookmarks.

users table:

bookmarks table:

Your Task:
Write a SQL query to find the email addresses of all users who signed up but have never created a bookmark.

Take a few minutes to construct your query. Remember to use aliases!

SELECT
    u.email
FROM
    users AS u
LEFT JOIN
    bookmarks AS b ON u.user_id = b.user_id
WHERE
    b.bookmark_id IS NULL;

Conclusion

Great job! You've now added one of the most versatile types of JOIN to your skillset. You've moved beyond simply connecting data to actively finding gaps and absences in that data—a leap forward in analytical capability.

Key Takeaways:

• LEFT JOIN keeps all rows from the left table, regardless of whether a match is found in the right table.
• When no match is found, columns from the right table are filled with NULL.
• The LEFT JOIN ... WHERE right_table.key IS NULL pattern is the standard way to identify records that lack a relationship, such as users who haven't performed a specific action.
• This is essential for analyzing user onboarding, feature adoption, and churn.

Preview of the Next Lesson

So far in this module, we've learned to combine tables with INNER JOIN and LEFT JOIN. The ON clause sets the linking condition, and we've just seen how WHERE can filter for NULLs. In the next lesson, we'll expand on this by learning how to apply the WHERE clause to filter the results of a joined table using a variety of conditions. This will allow you to answer much more specific questions, like "Show me all the 'button_click' events from users in Brazil who signed up in the last 30 days."