SELF JOIN

🎬 Scenario: Comparing Campaign Performance#

Your manager at Google Ads, Jordan, wants to analyze how different campaigns from the same company compare to each other.

Can you list all campaign pairs within each company so we can compare their performance?

Your task is to use a SELF JOIN to find all campaign comparisons for the same advertiser.

1. Understanding SELF JOIN#

A SELF JOIN joins a table to itself by treating it as if it were two separate tables.

Syntax

1SELECT a.column1, b.column2  
2FROM table_name a  
3JOIN table_name b  
4ON a.common_key = b.common_key;

• Assign aliases (a, b) to the table so it can be referenced twice.
• Use ON to define the relationship between the two rows.

2. Comparing Campaigns Within the Same Company#

Jordan wants to see all campaign comparisons within each company.

Example Query: Listing Campaign Comparisons for the Same Company

1SELECT a.company, a.campaign_name AS campaign_1, b.campaign_name AS campaign_2  
2FROM google_ads_campaigns a  
3JOIN google_ads_campaigns b  
4ON a.company = b.company  
5AND a.campaign_id < b.campaign_id;

What’s Happening?

• SELF JOIN finds pairs of campaigns within the same company.
• The condition a.campaign_id < b.campaign_id ensures each pair appears only once (avoids duplicates).

✍️ SQL Exercise#

Exercise: Comparing Campaigns Within the Same Company#

Jordan wants to compare all campaign pairs for each company.

Filter condition:

• Retrieve company name and two campaign names.
• Each company should appear with paired campaign comparisons.
• Sort by company in ascending order.

Write an SQL query to return the requested data.