UniversityAPI ReferenceJOIN and DEFAULT Advanced Patterns

JOIN and DEFAULT Advanced Patterns

API Reference
6 min read
Updated December 12, 2025

JOIN and DEFAULT Advanced Patterns

This guide covers advanced patterns for combining JOIN and DEFAULT options, plus real-world scenarios for handling orphan records (records with broken or missing relationships). These patterns are essential for building robust reports that gracefully handle incomplete data.

Understanding Orphan Records

Orphan records occur when a foreign key field points to a record that doesn't exist, or when the field is empty. This commonly happens when:

  • A customer is deleted but their orders remain
  • Data is imported with incomplete relationships
  • Users haven't filled in optional relation fields
  • Multi-hop paths break at any intermediate step

AXEL provides two strategies for handling orphans: DEFAULT values and JOIN modes.

Combining Options

You can combine VIA, JOIN, and DEFAULT in any order within a RELATED call. This flexibility allows you to build exactly the behavior you need.

VIA + DEFAULT

Use DEFAULT to provide meaningful fallback values when the relationship path is broken:

RELATED(
  [Departments][name],
  VIA(product, category, department),
  DEFAULT: "General"
)

What happens: If any hop in the path (product → category → department) is missing, AXEL returns "General" instead of NULL.

VIA + JOIN: INNER

Use INNER JOIN to exclude rows where the relationship doesn't exist:

RELATED(
  [Regions][name],
  VIA(customer, region),
  JOIN: INNER
)

What happens: Rows where the customer or region relationship is missing are excluded from the result entirely.

Full Combination

Combine all three options for complete control:

RELATED(
  [Regions][manager],
  VIA(customer, region),
  JOIN: LEFT,
  DEFAULT: "No Regional Manager"
)

What happens: All rows are included (LEFT JOIN), but orphans show "No Regional Manager" instead of NULL.

Options Order Doesn't Matter

AXEL parses options by keyword, so these are all equivalent:

RELATED([Table][col], VIA(field), JOIN: LEFT, DEFAULT: "X")
RELATED([Table][col], DEFAULT: "X", VIA(field), JOIN: LEFT)
RELATED([Table][col], JOIN: LEFT, DEFAULT: "X", VIA(field))

Use whatever order feels most readable for your team.

DEFAULT vs JOIN: INNER - Choosing the Right Strategy

These two approaches solve different problems. Choose based on your use case:

Scenario Use DEFAULT Use JOIN: INNER
Display user-friendly data ✓
Show all records with fallback ✓
Calculate accurate aggregates ✓
Exclude incomplete data ✓
Keep record count intact ✓
Data quality analysis ✓

When to Use DEFAULT

  • Reports and dashboards: Users expect to see all their data, even incomplete records
  • Export/download features: Users want everything, with clear indicators for missing data
  • Counting records: You need accurate record counts regardless of relationship status

When to Use JOIN: INNER

  • Financial calculations: Only include records with verified related data
  • Analysis accuracy: Exclude records that might skew results
  • Data cleanup workflows: Find only records with complete relationships

Real-World Scenario: Customer Order Management

This scenario demonstrates practical patterns for an order management system where data completeness varies.

Business Context

  • Some orders are walk-in sales (no customer account linked)
  • Some customers haven't set their region preference
  • Reports need to display meaningful data without confusing NULL values
  • Financial reports need accurate figures from verified accounts only

Complete Order Report with DEFAULT

This query creates a user-friendly report where every field has a meaningful value:

ADDCOLUMNS(
  [Orders],

  "Customer", RELATED(
    [Customers][name],
    VIA(customer),
    DEFAULT: "Walk-in Customer"
  ),

  "Email", RELATED(
    [Customers][email],
    VIA(customer),
    DEFAULT: ""
  ),

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

  "Regional Manager", RELATED(
    [Regions][manager],
    VIA(customer, region),
    DEFAULT: "Contact HQ"
  )
)

Result:

Order Customer Region Regional Manager
ORD001 John Smith North Sarah Johnson
ORD002 Walk-in Customer Unassigned Contact HQ
ORD003 Jane Doe Unassigned Contact HQ

Why this works:

  • ORD001 has complete data, so RELATED returns actual values
  • ORD002 has no customer link, so both customer and region default
  • ORD003 has a customer but no region, so only region-related fields default

Revenue Analysis with INNER JOIN

For financial accuracy, only count revenue from verified customer accounts:

-- Total revenue from verified customer orders only
SUMX(
  FILTER([Orders],
    NOT(ISBLANK(RELATED([Customers][id], VIA(customer), JOIN: INNER)))
  ),
  [amount]
)

Why use INNER here: Walk-in sales might have different processing rules, refund policies, or commission structures. Separating them ensures accurate reporting.

Comparing Both Approaches

-- All orders (for operations team)
"Total Orders", COUNTX([Orders], 1)

-- Orders with verified customers (for finance team)
"Verified Orders", COUNTX(
  FILTER([Orders],
    NOT(ISBLANK(RELATED([Customers][id], VIA(customer), JOIN: INNER)))
  ),
  1
)

RELATEDTABLE with JOIN Options

JOIN options also work with RELATEDTABLE for one-to-many relationships:

-- All customers, even those without orders (LEFT JOIN - default)
ADDCOLUMNS(
  [Customers],
  "Order Count", COUNTX(RELATEDTABLE([Orders], VIA(customer)), 1)
)

-- Only customers who have placed orders (INNER JOIN)
ADDCOLUMNS(
  FILTER([Customers],
    COUNTX(RELATEDTABLE([Orders], VIA(customer), JOIN: INNER), 1) > 0
  ),
  "Order Count", COUNTX(RELATEDTABLE([Orders], VIA(customer)), 1)
)

Best Practices

1. Choose Based on Use Case

Use Case Recommendation
User-facing reports DEFAULT with meaningful values
Data analysis/metrics JOIN: INNER for accuracy
Dashboards/visuals DEFAULT for cleaner display
Data exports DEFAULT with empty string ""
Troubleshooting No DEFAULT (see actual NULLs)

2. Be Consistent Within Reports

Use the same DEFAULT values throughout a report for the same type of missing data:

-- Good: Consistent DEFAULT handling across columns
ADDCOLUMNS([Orders],
  "Customer", RELATED([Customers][name], VIA(customer), DEFAULT: "Unknown"),
  "Region", RELATED([Regions][name], VIA(customer, region), DEFAULT: "Unknown")
)

-- Poor: Inconsistent defaults confuse users
ADDCOLUMNS([Orders],
  "Customer", RELATED([Customers][name], VIA(customer), DEFAULT: "N/A"),
  "Region", RELATED([Regions][name], VIA(customer, region), DEFAULT: "Unassigned")
)

3. Test with Edge Cases

Always verify your formulas handle these scenarios correctly:

  • Records with completely missing relationships
  • Empty related tables (no customers exist at all)
  • Multi-hop paths where middle hops are missing (customer exists but has no region)
  • Circular relationships (if your data model allows them)

4. Document Your Defaults

When using DEFAULT values, consider adding a legend or note to your report explaining what fallback values mean:

Note: "Walk-in Customer" indicates orders without a linked customer account.

Next Steps

← Back to UniversityGet Started