Joining Multiple Data Sources

Why this matters

Data Scientists regularly merge tables from product analytics, marketing, finance, and data warehouses. Good joins give you accurate features, cohorts, and metrics. Bad joins double-count users, drop revenue, or skew models. Mastering joins means you can confidently assemble the exact dataset you need.

• Build training datasets from multiple tables (events, users, subscriptions).
• Answer business questions (conversion by channel, LTV by cohort).
• Create clean features (latest plan, last payment date, total orders).

Concept explained simply

A SQL join lines up rows from two or more tables where key values match. Think of laying two spreadsheets side by side and keeping rows that correspond to the same entity (like a user_id). When there are multiple tables, you connect them one step at a time, usually via keys or bridge tables.

Mental model

• Start table: the “story you’re telling” (e.g., orders).
• Lookup table(s): add attributes (e.g., customers, products).
• Bridge table(s): resolve many-to-many (e.g., user_channel_map).
• Filters: push them early to reduce row explosion and speed up queries.

Core patterns and syntax

-- Basic multi-table join
SELECT o.order_id, c.customer_name, p.product_name
FROM orders o
JOIN order_items oi   ON o.order_id = oi.order_id
JOIN products p       ON oi.product_id = p.product_id
LEFT JOIN customers c ON o.customer_id = c.customer_id;

-- Pre-aggregate before join to avoid duplication
WITH payment_sum AS (
  SELECT order_id, SUM(amount) AS paid
  FROM payments
  GROUP BY order_id
)
SELECT o.order_id, o.total_amount, ps.paid
FROM orders o
LEFT JOIN payment_sum ps ON o.order_id = ps.order_id;

-- Date-range (temporal) join: active plan at purchase time
SELECT t.txn_id, p.plan_name
FROM transactions t
JOIN plans p
  ON t.user_id = p.user_id
 AND t.txn_ts >= p.valid_from
 AND (p.valid_to IS NULL OR t.txn_ts < p.valid_to);

-- Semi-join: users who purchased
SELECT u.user_id
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.user_id
);

-- Anti-join: users who never purchased
SELECT u.user_id
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.user_id
);

Worked examples

Example 1: Orders with customer and product details

Goal: Each order row with its customer name and the list of product names.

-- One row per order with concatenated product names (syntax may vary by SQL dialect)
WITH items AS (
  SELECT o.order_id,
         STRING_AGG(p.product_name, ', ') AS products
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  JOIN products p     ON oi.product_id = p.product_id
  GROUP BY o.order_id
)
SELECT o.order_id, c.customer_name, i.products
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
LEFT JOIN items i     ON o.order_id = i.order_id;

Example 2: Revenue with payments joined once

Goal: Show total order amount and amount paid. Payments table can contain multiple rows per order.

WITH payment_sum AS (
  SELECT order_id, SUM(amount) AS paid
  FROM payments
  GROUP BY order_id
)
SELECT o.order_id, o.total_amount, COALESCE(ps.paid, 0) AS paid
FROM orders o
LEFT JOIN payment_sum ps ON ps.order_id = o.order_id;

Example 3: Many-to-many via bridge (marketing channel)

Goal: Attach a primary acquisition channel to each user using a mapping table that can have multiple rows per user.

-- Choose the earliest channel by timestamp as primary
WITH ranked AS (
  SELECT ucm.user_id, ucm.channel, ucm.first_seen_ts,
         ROW_NUMBER() OVER (PARTITION BY ucm.user_id ORDER BY ucm.first_seen_ts) AS rn
  FROM user_channel_map ucm
)
SELECT u.user_id, r.channel AS primary_channel
FROM users u
LEFT JOIN ranked r
  ON u.user_id = r.user_id AND r.rn = 1;

Practical steps

1. Define the grain: Decide the target row-level (e.g., one row per order). Everything else must respect this.
2. Map relationships: Draw which tables connect and via which keys.
3. Pre-aggregate many-sides: SUM/COUNT or window to one row per key before joins.
4. Join in layers: Start with the main fact table, then add lookups and bridges.
5. Validate counts: Compare row counts before/after joins and totals to catch duplicates or losses.

Exercises

Use the following simplified schemas in mind:

customers(customer_id, customer_name, signup_ts)
orders(order_id, customer_id, order_ts, total_amount)
order_items(order_id, product_id, qty, unit_price)
products(product_id, product_name, category)
payments(payment_id, order_id, amount, paid_ts)
user_channel_map(user_id, channel, first_seen_ts)

WITH primary_channel AS (
  SELECT user_id AS customer_id, channel,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY first_seen_ts) AS rn
  FROM user_channel_map
),
channels AS (
  SELECT customer_id, channel FROM primary_channel WHERE rn = 1
),
orders_by_cust AS (
  SELECT o.customer_id, COUNT(*) AS orders_cnt, AVG(o.total_amount) AS aov
  FROM orders o
  GROUP BY o.customer_id
)
SELECT c.channel,
       COALESCE(SUM(oc.orders_cnt), 0) AS orders_count,
       AVG(oc.aov) FILTER (WHERE oc.aov IS NOT NULL) AS aov
FROM channels c
LEFT JOIN orders_by_cust oc ON oc.customer_id = c.customer_id
GROUP BY c.channel
ORDER BY c.channel;

channel | orders_count | aov
------- | ------------ | ----
ads     | 120          | 58.30
email   |  95          | 62.10
referral|  40          | 71.55

WITH payment_sum AS (
  SELECT order_id, SUM(amount) AS paid_amount
  FROM payments
  GROUP BY order_id
)
SELECT o.order_id,
       o.total_amount,
       COALESCE(ps.paid_amount, 0) AS paid_amount,
       CASE WHEN COALESCE(ps.paid_amount, 0) >= o.total_amount THEN 'Y' ELSE 'N' END AS is_fully_paid
FROM orders o
LEFT JOIN payment_sum ps ON ps.order_id = o.order_id
ORDER BY o.order_id;

order_id | total_amount | paid_amount | is_fully_paid
-------- | ------------ | ----------- | -------------
1001     | 120.00       | 120.00      | Y
1002     |  80.00       |  50.00      | N
1003     |  65.00       |   0.00      | N

Common mistakes and how to self-check

• Row explosion from many-to-many joins. Fix: Pre-aggregate or window to one row per key before joining.
• Filtering in WHERE after a LEFT join on right-table columns, which nullifies the LEFT. Fix: Move the condition into the ON clause or use IS NOT NULL carefully.
• Mismatched data types on join keys (string vs int). Fix: CAST keys to a common type.
• Joining on names instead of IDs. Fix: Use stable keys; add a mapping table if needed.
• Not defining the grain. Fix: Write the intended grain at the top of the query as a comment and design joins accordingly.

Practical projects

• Customer 360 view: Build a single table keyed by customer_id with latest plan, total orders, lifetime spend, last seen channel.
• Marketing attribution snapshot: For each order, attach acquisition channel and compute revenue by channel.
• Product performance mart: One row per product with units sold, revenue, unique buyers, and repeat purchase rate.

Mini challenge

Build a query returning one row per customer with: total_orders, lifetime_value (sum total_amount), first_order_ts, last_order_ts, and primary_channel (earliest channel). Ensure customers with no orders still appear with zero totals.

WITH primary_channel AS (
  SELECT user_id AS customer_id, channel,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY first_seen_ts) AS rn
  FROM user_channel_map
), pc AS (
  SELECT customer_id, channel FROM primary_channel WHERE rn = 1
), agg_orders AS (
  SELECT customer_id,
         COUNT(*) AS total_orders,
         COALESCE(SUM(total_amount),0) AS lifetime_value,
         MIN(order_ts) AS first_order_ts,
         MAX(order_ts) AS last_order_ts
  FROM orders
  GROUP BY customer_id
)
SELECT c.customer_id,
       COALESCE(ao.total_orders, 0) AS total_orders,
       COALESCE(ao.lifetime_value, 0) AS lifetime_value,
       ao.first_order_ts,
       ao.last_order_ts,
       pc.channel AS primary_channel
FROM customers c
LEFT JOIN ao ON ao.customer_id = c.customer_id
LEFT JOIN pc ON pc.customer_id = c.customer_id;

Who this is for

• Data Scientists and Analysts who assemble datasets from multiple tables.
• Engineers building feature stores or analytics marts.
• Anyone preparing data for modeling, dashboards, or experiments.

Prerequisites

• Comfort with SELECT, WHERE, GROUP BY, and basic joins.
• Basic understanding of primary/foreign keys and one-to-many vs many-to-many relationships.
• Familiarity with window functions is a plus.

Learning path

1. Review join types and ON vs WHERE behavior.
2. Practice multi-table joins with careful grain definition.
3. Use CTEs for pre-aggregation and row selection via window functions.
4. Validate results with row counts and control totals.
5. Tackle temporal joins and semi/anti joins.

Next steps

• Re-implement the examples using your data warehouse dialect.
• Add unit checks: compare totals before and after joins.
• Move on to window functions and advanced aggregations.