SQL Joins in BigQuery: How to Combine Marketing Data Tables

A JOIN is the SQL operation that merges two or more tables based on a shared column. In BigQuery, you use JOINs to combine your marketing data sources: Meta Ads with Google Ads, ad data with CRM data, campaign data with revenue data.

Understanding JOINs is one of the most valuable SQL skills for marketing analysts. Here is how they work.

The Concept: Venn Diagrams and Sets

The easiest way to understand SQL JOINs is through Venn diagrams. A Venn diagram shows two sets (two circles) and the overlap between them.

Your two tables are the two circles. The shared column is what the circles have in common. The JOIN type determines which part of the Venn diagram you return.

There are four main JOIN types. Each returns a different portion of the combined data.

INNER JOIN: Only Matching Rows

An INNER JOIN returns only the rows that exist in both tables. If a row exists in Table A but not in Table B, it is excluded. If a row exists in Table B but not in Table A, it is also excluded.

• In Venn diagram terms: INNER JOIN returns only the overlapping area in the center.
• Marketing example: you want to see only the campaigns that have data in both your Meta Ads table and your Google Ads table.

SELECT
meta.date,
meta.campaign_name,
meta.spend AS meta_spend,
gads.cost AS google_spend
FROM your_dataset.meta_ads AS meta
INNER JOIN your_dataset.google_ads AS gads

ON meta.date = gads.date

AND meta.campaign_name = gads.campaign_name

This returns only rows where the same campaign name and date appear in both tables.

LEFT JOIN: All Rows From the Left Table

A LEFT JOIN returns all rows from the left table (the one in FROM) and the matching rows from the right table. If a row in the left table has no match in the right table, the right table columns appear as NULL.

• In Venn diagram terms: LEFT JOIN returns the entire left circle, including the part that does not overlap.
• Marketing example: you want all your Meta Ads campaigns, even if they do not have a matching row in your Google Ads table.

SELECT
meta.date,
meta.campaign_name,
meta.spend AS meta_spend,
gads.cost AS google_spend
FROM your_dataset.meta_ads AS meta
LEFT JOIN your_dataset.google_ads AS gads

ON meta.date = gads.date

AND meta.campaign_name = gads.campaign_name

Campaigns that exist only in Meta Ads appear in the result with NULL in the google_spend column. This is the most common JOIN type for marketing data because you want to preserve all your campaigns, even those running on only one platform.

RIGHT JOIN: All Rows From the Right Table

A RIGHT JOIN is the mirror of a LEFT JOIN. It returns all rows from the right table and the matching rows from the left table. Rows in the right table with no match in the left table appear with NULL in the left table columns.

In practice, most analysts rewrite RIGHT JOINs as LEFT JOINs by swapping the table order. They are functionally equivalent.

FULL JOIN: All Rows From Both Tables

A FULL JOIN (also called FULL OUTER JOIN) returns all rows from both tables. Where there is a match, the columns from both tables are populated. Where there is no match, the columns from the missing side appear as NULL.

• In Venn diagram terms: FULL JOIN returns both circles in their entirety, including the non-overlapping parts.
• Marketing example: you want all campaigns from both Meta Ads and Google Ads, regardless of whether they match.

SELECT
COALESCE(meta.date, gads.date) AS date,
COALESCE(meta.campaign_name, gads.campaign_name) AS campaign_name,
meta.spend AS meta_spend,
gads.cost AS google_spend
FROM your_dataset.meta_ads AS meta
FULL JOIN your_dataset.google_ads AS gads

ON meta.date = gads.date

AND meta.campaign_name = gads.campaign_name

COALESCE returns the first non-NULL value from the list. This handles rows that exist in only one table by using the available value for date and campaign name.

Choosing the Right JOIN for Marketing Data

For most marketing data analysis, LEFT JOIN is the right choice. It preserves all your campaigns from the primary table and adds data from secondary tables where it exists.

Use INNER JOIN when you only want campaigns that exist in both tables and you want to exclude campaigns that are unique to one platform.

Use FULL JOIN when you want a complete view of all campaigns across all platforms, including those that run on only one platform.

Joining Ad Data With CRM Data

JOINs become especially valuable when you combine ad performance data with CRM or revenue data.

SELECT

ads.date,

ads.campaign_name,

ads.spend,

ads.leads AS ad_reported_leads,

crm.qualified_leads,

crm.revenue,

ROUND(ads.spend / NULLIF(crm.revenue, 0), 2) AS true_roas

FROM your_dataset.meta_ads AS ads

LEFT JOIN your_dataset.hubspot_data AS crm

ON ads.campaign_name = crm.utm_campaign

AND ads.date = crm.date

This query joins Meta Ads spend data with HubSpot CRM data on campaign name and date. The result shows true ROAS calculated from actual revenue, not platform-reported conversion value.

This type of analysis is only possible in BigQuery, where all your data lives in one place and you control the JOIN logic.