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.
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
%pip install -qU pixeltable
import pixeltable as pxtimport pixeltable.functions as pxtf# Create a fresh directorypxt.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’.
<pixeltable.catalog.dir.Dir at 0x148e73850>
Created table ‘orders’.
Inserted 4 rows with 0 errors in 0.01 s (657.81 rows/s)
Inner join (matching rows only)
# Inner join: only rows that match in both tablescustomers.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)
# Left join: all customers, with order data where available# Charlie has no orders, so product/amount will be nullcustomers.join( orders, on=customers.customer_id == orders.customer_id, how='left').select(customers.name, orders.product, orders.amount).collect()
Join with filtering
# Combine join with where clause to filter resultscustomers.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
# Join and aggregate: total spending per customercustomers.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)
# 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
# Build a join query and collect as DataFramecustomer_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
# Create a new table from the DataFrameorders_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.
# Page 1: first 2 rowsorders.order_by(orders.order_id).limit(2).collect()
# Page 2: next 2 rows (skip the first 2)orders.order_by(orders.order_id).limit(2, offset=2).collect()
from pixeltable.functions import sum, count, mean, min, max# Use as functions, not methodstotal=sum(t.amount)num_rows=count(t.id)
Saving join results:
# Collect as DataFrame, then create tabledf = query.select(name=t.col, ...).collect().to_pandas()new_table = pxt.create_table('path', source=df)
Pagination:
# limit(n) returns at most n rows# limit(n, offset=k) skips the first k rows, then returns nquery.order_by(t.id).limit(10) # rows 0-9query.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