Join Tables

Every session on this page joins the same two tables. orders has three rows and customers has three, and they overlap on two customer_id values: o3 points at c9, which no customer has, and c3 has no order. What each join does with those two odd rows is the whole difference between them.

orders                        customers
order_id  customer_id  amount   customer_id  name
o1        c1           10       c1           Ada
o2        c2           20       c2           Grace
o3        c9           5        c3           Cy

Join two tables

❯make a new table that takes the orders and customers tables and joins them on the customer_id column, dropping the orders with no customer

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — two input tables, so the transformer takes a slot each, and join() on customer_id. Dropping the unmatched orders is the default, how="inner", so it can be left off.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["orders", "customers"],
  output_tables=["order_names"],
)
def join_orders(orders: TableFrameSpec, customers: TableFrameSpec) -> TableFrameSpec:
  return orders.join(customers, on="customer_id")

⏺

Execution committed, 0 failures.

⏺

subscriptions/order_names is live — 2 of 3 orders. o3 points at c9 and dropped out, and c3 never appears because it has no order:

┌──────────┬─────────────┬────────┬───────┐
│ order_id │ customer_id │ amount │ name  │
├──────────┼─────────────┼────────┼───────┤
│ o1       │ c1          │ 10     │ Ada   │
├──────────┼─────────────┼────────┼───────┤
│ o2       │ c2          │ 20     │ Grace │
└──────────┴─────────────┴────────┴───────┘

Transformed tables

orders

order_id	customer_id	amount
o1	c1	10
o2	c2	20
o3	c9	5

3 rows

customers

customer_id	name
c1	Ada
c2	Grace
c3	Cy

3 rows

order_names

order_id	customer_id	amount	name
o1	c1	10	Ada
o2	c2	20	Grace

2 rows

Join on more than one column

❯make a new table that takes the visits and targets tables and joins them on both the customer_id and date columns

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — on takes a list when the key is more than one column, and a row matches only when every column in the list agrees.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["visits", "targets"],
  output_tables=["visits_vs_target"],
)
def join_on_day(visits: TableFrameSpec, targets: TableFrameSpec) -> TableFrameSpec:
  return visits.join(targets, on=["customer_id", "date"])

⏺

Execution committed, 0 failures.

⏺

subscriptions/visits_vs_target is live — the c1/tue visit had no target row for that day, so it dropped:

┌─────────────┬──────┬───────┬────────┐
│ customer_id │ date │ spend │ target │
├─────────────┼──────┼───────┼────────┤
│ c1          │ mon  │ 10    │ 12     │
├─────────────┼──────┼───────┼────────┤
│ c2          │ mon  │ 7     │ 5      │
└─────────────┴──────┴───────┴────────┘

Transformed tables

visits

customer_id	date	spend
c1	mon	10
c2	mon	7
c1	tue	4

3 rows

targets

customer_id	date	target
c1	mon	12
c2	mon	5

2 rows

visits_vs_target

customer_id	date	spend	target
c1	mon	10	12
c2	mon	7	5

2 rows

Handle duplicate column names

❯make a new table that takes the orders and returns tables and joins them on the id column, renaming the amount column that both of them carry

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — both sides carry amount, and only the join key is merged into one column. suffix renames the right-hand copy of every other collision.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["orders", "returns"],
  output_tables=["orders_returns"],
)
def join_returns(orders: TableFrameSpec, returns: TableFrameSpec) -> TableFrameSpec:
  return orders.join(returns, on="id", suffix="_return")

⏺

Execution committed, 0 failures.

⏺

subscriptions/orders_returns is live — id appears once, and the returns table's amount came through as amount_return:

┌────┬────────┬───────────────┐
│ id │ amount │ amount_return │
├────┼────────┼───────────────┤
│ o1 │ 10     │ 4             │
├────┼────────┼───────────────┤
│ o2 │ 20     │ 20            │
└────┴────────┴───────────────┘

Transformed tables

orders

id	amount
o1	10
o2	20

2 rows

returns

id	amount
o1	4
o2	20

2 rows

orders_returns

id	amount	amount_return
o1	10	4
o2	20	20

2 rows