UniversityAPI ReferenceJOIN and DEFAULT Options

JOIN and DEFAULT Options

API Reference
3 min read
Updated December 12, 2025

JOIN and DEFAULT Options

When working with relationship functions, you may encounter orphan records - records where the relationship doesn't exist. AXEL provides JOIN and DEFAULT options to handle these.

Understanding Orphan Records

An orphan record is a record where:

  • The relation field is empty (not filled in)
  • The relation field points to a deleted record
  • Any hop in a multi-hop VIA path is broken

JOIN Options

JOIN: LEFT (Default)

Behavior: Include all records, return NULL for orphans.

RELATED(Table[Column], JOIN: LEFT)

This is the default - you don't need to specify it explicitly.

Example:

ADDCOLUMNS(
  [Orders],
  "Customer Name", RELATED([Customers][name], VIA(customer), JOIN: LEFT)
)
Order ID Amount Customer Name
ORD001 150 John Smith
ORD002 89 NULL
ORD003 245 Jane Doe

JOIN: INNER

Behavior: Exclude orphan records entirely - they are skipped.

RELATED(Table[Column], JOIN: INNER)

Example:

ADDCOLUMNS(
  [Orders],
  "Customer Name", RELATED([Customers][name], VIA(customer), JOIN: INNER)
)
Order ID Amount Customer Name
ORD001 150 John Smith
ORD003 245 Jane Doe

(ORD002 excluded - no customer)

When to Use INNER JOIN

  • Calculating aggregates where orphans should not be counted
  • Only want records with complete relationship data
  • NULL values would skew your analysis
-- Only sum orders with valid customers
SUMX(
  [Orders],
  [amount] * RELATED([Customers][loyalty_multiplier], VIA(customer), JOIN: INNER)
)

DEFAULT Option

Provide a fallback value for orphan records while keeping the record in results.

Syntax

RELATED(Table[Column], DEFAULT: "fallback_value")
RELATED(Table[Column], DEFAULT: 0)
RELATED(Table[Column], DEFAULT: BLANK)

Supported Types

Type Example
String DEFAULT: "Unknown"
Number DEFAULT: 0
Decimal DEFAULT: 0.0
BLANK DEFAULT: BLANK

String Default

ADDCOLUMNS(
  [Orders],
  "Customer Name", RELATED([Customers][name], VIA(customer), DEFAULT: "Walk-in")
)
Order ID Customer Name
ORD001 John Smith
ORD002 Walk-in

Numeric Default

ADDCOLUMNS(
  [Products],
  "Discount", RELATED([Categories][discount_rate], VIA(category), DEFAULT: 0)
)

Multi-hop with Default

RELATED([Regions][name], VIA(customer, region), DEFAULT: "Unassigned")

If either hop fails (no customer OR customer has no region), the default is used.

Next Steps

← Back to UniversityGet Started