Scenario	Tables	Join on
Customer orders	`customers`, `orders`	`customer_id`
Product catalog	`products`, `inventory`	`sku`
Media library	`videos`, `captions`	`video_id`
User activity	`users`, `events`	`user_id`

customer_id	name	email
1	Alice	alice@example.com
2	Bob	bob@example.com
3	Charlie	charlie@example.com

order_id	customer_id	product	amount
101	1	Laptop	999.
102	1	Mouse	29.
103	2	Keyboard	79.
104	4	Monitor	299.

name	product	amount
Alice	Laptop	999.
Alice	Mouse	29.
Bob	Keyboard	79.

name	product	amount
Alice	Laptop	999.
Alice	Mouse	29.
Bob	Keyboard	79.
Charlie	NaN	NaN

name	email	product	amount
Alice	alice@example.com	Laptop	999.
Bob	bob@example.com	Keyboard	79.

name	total_spent	order_count
Alice	1028.	2
Bob	79.	1

name	product	price
Alice	Widget	19.99
Bob	Widget	19.99
Charlie	Widget	19.99
Alice	Gadget	29.99
Bob	Gadget	29.99
Charlie	Gadget	29.99

	name	email	product	amount
0	Alice	alice@example.com	Laptop	999.0
1	Alice	alice@example.com	Mouse	29.0
2	Bob	bob@example.com	Keyboard	79.0

name	email	product	amount
Alice	alice@example.com	Laptop	999.
Alice	alice@example.com	Mouse	29.
Bob	bob@example.com	Keyboard	79.

order_id	customer_id	product	amount
101	1	Laptop	999.
102	1	Mouse	29.

order_id	customer_id	product	amount
103	2	Keyboard	79.
104	4	Monitor	299.

Type	Keeps	Use when
`'inner'`	Matching rows only	You need data from both tables
`'left'`	All left + matching right	Keep all from first table
`'full_outer'`	All from both	Need complete picture
`'cross'`	All combinations	Cartesian product

## Solution **What’s in this recipe:** * Inner join to match rows from both tables * Left join to keep all rows from the first table * Cross join for Cartesian product (all combinations) * Join with filtering, aggregation, and saving results * Paginate results with `limit()` and `offset` Use `table1.join(table2, on=..., how=...)` to combine tables based on matching columns. ### Setup ```python theme={null} %pip install -qU pixeltable ``` ```python theme={null} import pixeltable as pxt import pixeltable.functions as pxtf ``` ```python theme={null} # Create a fresh directory pxt.drop_dir('join_demo', force=True) pxt.create_dir('join_demo') ```

  Connected to Pixeltable database at: postgresql+psycopg://postgres:@/pixeltable?host=/Users/pjlb/.pixeltable/pgdata
  Created directory 'join\_demo'.
  \

### Create sample tables ```python theme={null} # Create a customers table customers = pxt.create_table( 'join_demo/customers', {'customer_id': pxt.Int, 'name': pxt.String, 'email': pxt.String}, ) customers.insert( [ {'customer_id': 1, 'name': 'Alice', 'email': 'alice@example.com'}, {'customer_id': 2, 'name': 'Bob', 'email': 'bob@example.com'}, { 'customer_id': 3, 'name': 'Charlie', 'email': 'charlie@example.com', }, ] ) customers.collect() ```

  Created table 'customers'.
  Inserted 3 rows with 0 errors in 0.01 s (385.68 rows/s)

```python theme={null} # Create an orders table orders = pxt.create_table( 'join_demo/orders', { 'order_id': pxt.Int, 'customer_id': pxt.Int, 'product': pxt.String, 'amount': pxt.Float, }, ) orders.insert( [ { 'order_id': 101, 'customer_id': 1, 'product': 'Laptop', 'amount': 999.00, }, { 'order_id': 102, 'customer_id': 1, 'product': 'Mouse', 'amount': 29.00, }, { 'order_id': 103, 'customer_id': 2, 'product': 'Keyboard', 'amount': 79.00, }, { 'order_id': 104, 'customer_id': 4, 'product': 'Monitor', 'amount': 299.00, }, # No matching customer ] ) orders.collect() ```

  Created table 'orders'.
  Inserted 4 rows with 0 errors in 0.01 s (657.81 rows/s)

### Inner join (matching rows only) ```python theme={null} # Inner join: only rows that match in both tables customers.join( orders, on=customers.customer_id == orders.customer_id, how='inner' ).select(customers.name, orders.product, orders.amount).collect() ```

### Left join (keep all from first table) ```python theme={null} # Left join: all customers, with order data where available # Charlie has no orders, so product/amount will be null customers.join( orders, on=customers.customer_id == orders.customer_id, how='left' ).select(customers.name, orders.product, orders.amount).collect() ```

### Join with filtering ```python theme={null} # Combine join with where clause to filter results customers.join( orders, on=customers.customer_id == orders.customer_id, how='inner' ).where(orders.amount > 50).select( customers.name, customers.email, orders.product, orders.amount ).collect() ```

### Join with aggregation ```python theme={null} # Join and aggregate: total spending per customer customers.join( orders, on=customers.customer_id == orders.customer_id, how='inner' ).group_by(customers.name).select( customers.name, total_spent=pxtf.sum(orders.amount), order_count=pxtf.count(orders.order_id), ).collect() ```

### Cross join (all combinations) ```python theme={null} # Cross join: every customer paired with every product (no 'on' condition) products = pxt.create_table( 'join_demo/products', {'product': pxt.String, 'price': pxt.Float} ) products.insert( [ {'product': 'Widget', 'price': 19.99}, {'product': 'Gadget', 'price': 29.99}, ] ) customers.join(products, how='cross').select( customers.name, products.product, products.price ).collect() ```

  Created table 'products'.
  Inserted 2 rows with 0 errors in 0.00 s (422.52 rows/s)

### Save join results to a new table ```python theme={null} # Build a join query and collect as DataFrame customer_orders_df = ( customers.join( orders, on=customers.customer_id == orders.customer_id, how='inner', ) .select( name=customers.name, email=customers.email, product=orders.product, amount=orders.amount, ) .collect() .to_pandas() ) customer_orders_df ```

```python theme={null} # Create a new table from the DataFrame orders_report = pxt.create_table( 'join_demo/orders_report', source=customer_orders_df ) orders_report.collect() ```

  Created table 'orders\_report'.
  Inserted 3 rows with 0 errors in 0.01 s (500.32 rows/s)

### Paginate results with limit and offset Use `limit(n, offset=k)` to retrieve results in pages. This is useful for displaying results incrementally or building paginated APIs. ```python theme={null} # Page 1: first 2 rows orders.order_by(orders.order_id).limit(2).collect() ```

```python theme={null} # Page 2: next 2 rows (skip the first 2) orders.order_by(orders.order_id).limit(2, offset=2).collect() ```

## Explanation **Join types:**

**Join syntax:** ```python theme={null} # Simple: join on column by name t1.join(t2, on=t1.id) # Explicit predicate t1.join(t2, on=t1.customer_id == t2.customer_id) # Composite key t1.join(t2, on=(t1.pk1 == t2.pk1) & (t1.pk2 == t2.pk2)) ``` **Aggregation functions:** ```python theme={null} from pixeltable.functions import sum, count, mean, min, max # Use as functions, not methods total=sum(t.amount) num_rows=count(t.id) ``` **Saving join results:** ```python theme={null} # Collect as DataFrame, then create table df = query.select(name=t.col, ...).collect().to_pandas() new_table = pxt.create_table('path', source=df) ``` **Pagination:** ```python theme={null} # limit(n) returns at most n rows # limit(n, offset=k) skips the first k rows, then returns n query.order_by(t.id).limit(10) # rows 0-9 query.order_by(t.id).limit(10, offset=10) # rows 10-19 ``` **Tips:** * Use explicit predicates (`t1.col == t2.col`) for clarity * Chain `.where()` after join to filter results * Chain `.group_by()` for aggregations * Use `'left'` join when the first table is your “main” table * Use named columns in `.select(name=col)` for clean column names * Always use `.order_by()` with pagination to get deterministic page ordering ## See also * [Look up structured data](/howto/cookbooks/agents/pattern-data-lookup) - Use retrieval UDFs for lookups * [Sample data for training](/howto/cookbooks/data/data-sampling) - Sample from joined results Built with [Mintlify](https://mintlify.com).