Documentation

7. Joins

Combine rows from multiple tables using foreign keys or explicit column mappings.

Setup — two related tables

The examples on this page use a small users + orders schema with a foreign key relationship.

from relpy import RelPy, AutoNumber, col

db = RelPy()

db.create_table("users")
db.add_column("users", "id",   AutoNumber, is_primary_key=True)
db.add_column("users", "name", str, nullable=False)
db.add_column("users", "city", str, nullable=False)

db.create_table("orders")
db.add_column("orders", "id",      AutoNumber, is_primary_key=True)
db.add_column("orders", "user_id", int, nullable=False, references="users.id")
db.add_column("orders", "amount",  float, nullable=False)
db.add_column("orders", "status",  str, nullable=False)

db.insert("users", {"name": "Alice", "city": "Tel Aviv"})   # id=1
db.insert("users", {"name": "Bob",   "city": "Haifa"})      # id=2
db.insert("users", {"name": "Carol", "city": "Jerusalem"})  # id=3

db.insert("orders", {"user_id": 1, "amount": 120.0, "status": "paid"})
db.insert("orders", {"user_id": 1, "amount":  80.0, "status": "paid"})
db.insert("orders", {"user_id": 2, "amount": 200.0, "status": "pending"})
# Carol has no orders

Automatic FK join

When a foreign key exists between two tables, RelPyDB can infer the join column automatically.

RelPyDB — FK inferred

result = (
    db.query("orders")
      .join("users")
      .select(
          "orders.id",
          "users.name",
          "orders.amount",
          "orders.status",
      )
      .to_list()
)

SQL equivalent

SELECT
  orders.id,
  users.name,
  orders.amount,
  orders.status
FROM orders
JOIN users
  ON orders.user_id = users.id;

Output — 3 rows (only users with orders)
orders.idusers.nameorders.amountorders.status
1Alice120.0paid
2Alice80.0paid
3Bob200.0pending

orders.id	users.name	orders.amount	orders.status
1	Alice	120.0	paid
2	Alice	80.0	paid
3	Bob	200.0	pending

Explicit join columns

# using on=
db.query("orders").join("users", on=("user_id", "id"))

# using left_on / right_on
db.query("orders").join("users", left_on="user_id", right_on="id")

Use these when the foreign key doesn't exist in the schema, or when joining on a non-FK column.

Join types

method= value	Shortcut	Behaviour
`"inner"` (default)	`inner_join("users")`	Only rows with a match on both sides.
`"left"`	`left_join("users")`	All rows from the left table; `None` for unmatched right columns.
`"right"`	`right_join("users")`	All rows from the right table; `None` for unmatched left columns.
`"full"`	`full_join("users")`	All rows from both sides; `None` where there is no match.
`"cross"`	`cross_join("users")`	Cartesian product — every left row combined with every right row.
`"natural"`	`natural_join("users")`	Joins on all columns with the same name in both tables.

LEFT JOIN example — include users with no orders

RelPyDB

result = (
    db.query("users")
      .left_join("orders")
      .select(
          "users.name",
          "orders.amount",
          "orders.status",
      )
      .to_list()
)

SQL equivalent

SELECT
  users.name,
  orders.amount,
  orders.status
FROM users
LEFT JOIN orders
  ON orders.user_id = users.id;

Output — Carol appears with None values
users.nameorders.amountorders.status
Alice120.0paid
Alice80.0paid
Bob200.0pending
CarolNoneNone

users.name	orders.amount	orders.status
Alice	120.0	paid
Alice	80.0	paid
Bob	200.0	pending
Carol	None	None

Chained joins

You can chain multiple .join() calls to bring in more than two tables.

# three-table join: order_items → orders → products
result = (
    db.query("order_items")
      .join("orders")
      .join("products")
      .select(
          "orders.id",
          "products.name",
          "order_items.quantity",
          "order_items.price",
      )
      .to_list()
)

Filtering a join result

RelPyDB

paid_orders = (
    db.query("orders")
      .join("users")
      .where(col("orders.status") == "paid")
      .select(
          "users.name",
          "orders.amount",
      )
      .order_by("orders.amount", descending=True)
      .to_list()
)

SQL equivalent

SELECT users.name, orders.amount
FROM orders
JOIN users
  ON orders.user_id = users.id
WHERE orders.status = 'paid'
ORDER BY orders.amount DESC;

Output
users.nameorders.amount
Alice120.0
Alice80.0

users.name	orders.amount
Alice	120.0
Alice	80.0

← 6. Querying 8. Grouping →