How joins are generated

IntroductionCopy Anchor Link

You never write a join in AQL. You declare relationships once, in the dataset, and the compiler derives the joins each query needs. To be sure the results are what you expect, it helps to know how that derivation works.

How SQL is generated for each join typeCopy Anchor Link

By default, the compiler uses LEFT JOIN (also known as LEFT OUTER JOIN) for both many-to-one and one-to-one relationships. For many-to-one, the model on the "many" side goes on the left of the join. If you have verified that a relationship's keys always match, you can opt into INNER JOIN instead with nullable=false.

Consider an example where we want to sum revenue by the gender of the user who placed the order:

explore {
  dimensions { users.gender }
  measures { total_revenue: sum(orders.revenue) }
}

The generated SQL looks like this:

SELECT
  U.gender,
  SUM(O.revenue) as total_orders
FROM orders O
LEFT JOIN users U ON O.user_id = U.id
GROUP BY 1

Fan-out with one-to-many relationshipsCopy Anchor Link

Fan-out happens when a join duplicates rows on the "one" side of a one-to-many relationship. For example, joining orders to order_items repeats each order once per item. An aggregate like SUM(orders.delivery_attempts) then counts the same order multiple times and returns an inflated result.

The compiler detects and resolves this automatically. Every relationship declares its cardinality, so the compiler knows when a join path could duplicate rows. It then restructures the query: aggregate first, then join the deduplicated results. This works for any measure whose aggregation the compiler can see, which is every measure written in AQL.

For the full mechanism and how to fix that error, see Fan-out resolution.

Referential integrity and join typesCopy Anchor Link

Take the (Orders, Users) example above. Consider scenario where there are orders records of user ID = 5, but in the users model, no corresponding record of user ID 5 found. This is a violation of the referential integrity rule between orders and users (many-to-one relationship).

If we use INNER JOIN for the above query, the result set would eliminate orders rows with unfounded users. This is dangerous and will underreport the sales results.

Therefore, using INNER JOIN does not resolve referential integrity violation correctly.

-- IMPORTANT: This is not what the compiler does
-- Using INNER JOIN
SELECT
  U.gender,
  SUM(O.revenue) as total_orders
FROM orders O
INNER JOIN users U ON O.user_id = U.id
GROUP BY 1

Generally speaking, the compiler opts for OUTER JOIN when dealing with referential integrity issues. In some situation, this will add query performance overhead, but it ensures that all records will be accounted for and not fall into referential integrity violation traps.

Opting into INNER JOIN with nullable=falseCopy Anchor Link

If you have verified that your data has no such violations, you can declare it by adding nullable=false to the relationship:

Dataset sales {
  ...
  relationships: [
    // Default (nullable=true): generates LEFT JOIN
    relationship(sales_fact.store_id > stores.id, true),

    // Non-nullable: generates INNER JOIN
    relationship(sales_fact.date > date_dimension.date, true, nullable=false)
  ]
}

nullable is an assertion about your data, not a join instruction. Setting it to false declares that the joining column on the "many" side is never NULL and every value matches exactly one row in the target model. Under that assertion, INNER JOIN returns exactly the same results as LEFT JOIN (there are no unmatched rows to drop), so the compiler can safely generate the faster join type.

Why INNER JOIN is fasterCopy Anchor Link

Data warehouses optimize INNER JOIN much more aggressively than LEFT JOIN. Filters on the dimension side can be pushed through the join into the fact table scan, partition pruning works normally (ClickHouse, for example, skips pruning when the filter sits on the right-hand side of a LEFT JOIN), and the join processes far fewer rows, which reduces memory pressure.

Take a query that sums sales, filtered by a date dimension field. With the default nullable=true:

SELECT SUM("sales_fact"."SalesAmount") AS "sales_amount"
FROM Sales_Fact "sales_fact"
LEFT JOIN Date_Dimension "date_dimension"
  ON "sales_fact"."Date" = "date_dimension"."Date"
WHERE "date_dimension"."Date" >= '2026-02-01'
  AND "date_dimension"."Date" < '2026-03-01'

With nullable=false on the relationship:

SELECT SUM("sales_fact"."SalesAmount") AS "sales_amount"
FROM Sales_Fact "sales_fact"
INNER JOIN Date_Dimension "date_dimension"
  ON "sales_fact"."Date" = "date_dimension"."Date"
WHERE "date_dimension"."Date" >= '2026-02-01'
  AND "date_dimension"."Date" < '2026-03-01'

Both queries return the same result when referential integrity holds. The INNER JOIN version, however, lets the warehouse prune partitions on the fact table and push the date filter down, which can turn a full-table scan into a small ranged read. On large, partitioned fact tables the difference is often an order of magnitude in both query time and memory usage.

What happens when the assertion is wrongCopy Anchor Link

If the data actually contains NULL or orphaned foreign keys, nullable=false makes the INNER JOIN drop those fact rows silently. There is no error or warning; totals simply shrink whenever the join is involved.

Consider this data, with relationship(orders.product_id > products.id, true, nullable=false):

orders

products

"Total amount" on its own needs no join and returns 190. "Total amount by product name" requires the join, and the INNER JOIN drops order 3, returning 150. The two reports disagree with no error anywhere. With the default nullable=true, the breakdown would instead show a NULL product group with amount 40, and both reports would agree at 190.

The compiler does not validate the assertion against your data, so keeping it truthful is the modeler's responsibility.

When it is safe to useCopy Anchor Link

Use nullable=false only when both of these hold:

1. The foreign-key column is NOT NULL, or you have verified there are no NULLs.
2. Every foreign-key value exists in the target model: an enforced foreign-key constraint, or a verified guarantee such as a date dimension covering all fact dates.

A quick check to run before enabling it:

SELECT COUNT(*)
FROM sales_fact f
LEFT JOIN date_dimension d ON f.date = d.date
WHERE d.date IS NULL;
-- Must return 0. Otherwise nullable=false will drop these rows.