LEFT & RIGHT JOIN

🎬 Scenario: Identifying Users Without Campaigns#

Your manager at Google Ads, Jordan, wants to analyze all advertisers, including those who haven’t launched any ad campaigns yet.

I need a report that includes all registered advertisers, even if they don’t have active campaigns. Can you make sure those users appear in the report?

Your task is to use LEFT JOIN to include users with and without campaigns.

Table: google_ads_campaigns

Table: google_ads_users

1. Understanding LEFT JOIN & RIGHT JOIN#

Unlike INNER JOIN, which only returns matching records, LEFT JOIN and RIGHT JOIN keep unmatched rows from one table.

Syntax

1SELECT table1.column1, table2.column2  
2FROM table1  
3LEFT JOIN table2  
4ON table1.common_key = table2.common_key;

• LEFT JOIN keeps all rows from the left table, filling in NULL for non-matching rows from the right table.
• RIGHT JOIN does the same but keeps all rows from the right table.

2. Using LEFT JOIN to Include All Users#

We want to list all advertisers, even those without ad campaigns.

Example Query: Showing Users With and Without Campaigns

1SELECT u.user_id, u.name, u.company, c.campaign_name, c.budget  
2FROM google_ads_users u  
3LEFT JOIN google_ads_campaigns c  
4ON u.user_id = c.user_id;

What’s Happening?

• LEFT JOIN keeps all users, even if they don’t have an ad campaign.
• Users without campaigns will have NULL for campaign_name and budget.

3. Using RIGHT JOIN to Include All Campaigns#

We can also ensure all campaigns appear, even if they aren’t linked to a user (hypothetically, if campaigns were assigned differently).

Example Query: Ensuring All Campaigns Are Listed

1SELECT c.campaign_id, c.campaign_name, u.name, u.company  
2FROM google_ads_campaigns c  
3RIGHT JOIN google_ads_users u  
4ON c.user_id = u.user_id;

What’s Happening?

• RIGHT JOIN keeps all campaigns, even if they aren’t linked to an active advertiser.
• Advertisers without campaigns will appear as NULL.

4. Joining More Than Two Tables#

Sometimes, we need to join multiple tables to get a more comprehensive dataset.

For example, to analyze advertisers, their campaigns, and clicks, we need to join:

• google_ads_users → Contains advertiser details.
• google_ads_campaigns → Contains campaign details.
• google_ads_clicks → Contains click activity.

Example Query: Connecting Users, Campaigns, and Clicks

1SELECT u.name, u.company, c.campaign_name, cl.click_id, cl.device_type, cl.converted  
2FROM google_ads_users u  
3LEFT JOIN google_ads_campaigns c  
4ON u.user_id = c.user_id  
5LEFT JOIN google_ads_clicks cl  
6ON c.campaign_id = cl.campaign_id;

What’s Happening?

• LEFT JOIN ensures that all advertisers appear in the dataset.
• Campaigns without clicks will have NULL values in the click_id column.

✍️ SQL Exercises#

Exercise 1: Listing All Advertisers, Even Without Campaigns#

Jordan needs a report that includes all advertisers, even if they haven’t launched a campaign.

Filter condition:

• Retrieve user name, company, and campaign name.
• Users without campaigns should still appear.
• Sort by company in ascending order.

Write an SQL query to return the requested data.

Exercise 2: Joining Multiple Tables for Click Data#

Jordan wants to see advertisers, their campaigns, and clicks.

Filter condition:

• Retrieve advertiser name, company, campaign name, and click ID.
• Include all advertisers, even if they haven’t received clicks.
• Sort by click_id in ascending order.

Write an SQL query to return the requested data.