> ## Documentation Index > Fetch the complete documentation index at: https://docs.pixeltable.com/llms.txt > Use this file to discover all available pages before exploring further. # Join tables to combine data > Join multiple Pixeltable tables on shared keys to combine metadata, embeddings, and computed columns into unified, queryable result sets. Open in Kaggle  Open in Colab  Download Notebook This documentation page is also available as an interactive notebook. You can launch the notebook in Kaggle or Colab, or download it for use with an IDE or local Jupyter installation, by clicking one of the above links. export const quartoRawHtml = [`
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
`]; Combine data from multiple tables using inner, left, and cross joins. ## Problem You have related data in separate tables and need to combine them for analysis—customers with orders, products with inventory, or media with metadata.
## 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={"theme":{"light":"light-plus","dark":"dark-plus"}} %pip install -qU pixeltable ``` ```python theme={"theme":{"light":"light-plus","dark":"dark-plus"}} import pixeltable as pxt import pixeltable.functions as pxtf # 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={"theme":{"light":"light-plus","dark":"dark-plus"}} # 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={"theme":{"light":"light-plus","dark":"dark-plus"}} # 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={"theme":{"light":"light-plus","dark":"dark-plus"}} # 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={"theme":{"light":"light-plus","dark":"dark-plus"}} # 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={"theme":{"light":"light-plus","dark":"dark-plus"}} # 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={"theme":{"light":"light-plus","dark":"dark-plus"}} # 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={"theme":{"light":"light-plus","dark":"dark-plus"}} # 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={"theme":{"light":"light-plus","dark":"dark-plus"}} # 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={"theme":{"light":"light-plus","dark":"dark-plus"}} # 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={"theme":{"light":"light-plus","dark":"dark-plus"}} # Page 1: first 2 rows orders.order_by(orders.order_id).limit(2).collect() ```
```python theme={"theme":{"light":"light-plus","dark":"dark-plus"}} # 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={"theme":{"light":"light-plus","dark":"dark-plus"}} # 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={"theme":{"light":"light-plus","dark":"dark-plus"}} 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={"theme":{"light":"light-plus","dark":"dark-plus"}} # 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={"theme":{"light":"light-plus","dark":"dark-plus"}} # 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