Join Types

Specify join types for relationships.

Overview

By default, relationships use INNER joins. You can specify different join types to handle optional relationships.

Join Type Options

inner (Default)

Returns only rows that match in both tables.

orders_customer:
  left: Orders
  right: Customer
  join: inner  # Optional, this is the default
  sql: left.customer_id = right.id
  cardinality: many_to_one

Use cases:

  • Required relationships
  • Most common case
  • Default if not specified

left

Returns all rows from left table, NULLs for non-matching right rows.

store_returns_reason:
  left: Store Returns
  right: Reason
  join: left
  sql: left.sr_reason_sk = right.r_reason_sk
  cardinality: many_to_one

Use cases:

  • Optional relationships
  • Nullable foreign keys
  • When right table may not exist

Returns all rows from right table, NULLs for non-matching left rows.

products_categories:
  left: Products
  right: Categories
  join: right
  sql: left.category_id = right.id
  cardinality: many_to_one

Use cases:

  • Less common
  • When you want all categories, even without products

Common Patterns

Required Relationship (INNER)

sales_customer:
  left: Sales
  right: Customer
  sql: left.customer_id = right.id
  cardinality: many_to_one
  # join: inner (default, not needed)

Optional Relationship (LEFT)

orders_promotion:
  left: Orders
  right: Promotion
  join: left
  sql: left.promotion_id = right.id
  cardinality: many_to_one

Why LEFT? Not all orders have promotions, but we want all orders.

Best Practices

  1. Use INNER by default - Most relationships are required
  2. Use LEFT for optional - When right table may not have matches
  3. Use RIGHT sparingly - Less common, can be confusing
  4. Document join types - Add comments explaining why

Next Steps