Query-Scoped Joins

Use subset join and union join to connect concepts for a single SELECT. Trilogy normally resolves relationships from the model; a scoped join supplies a relationship for an ad hoc query without changing later queries.

Syntax

Place join clauses after the select list and before grouping, HAVING, and ordering:

SELECT <dimensions and measures>
SUBSET JOIN <subset key> = <superset key>
UNION JOIN <left key> = <right key>
BY ROLLUP|CUBE|GROUPING SETS (...)
HAVING <output filter>
ORDER BY <expression> ASC|DESC
LIMIT <count>;

Only SELECT is required. Repeat join clauses when matching multiple keys. Each equality connects one pair of corresponding keys; measures remain separate columns in the output.

Subset Join: Enrich a Complete Domain

subset join a = b declares that the values of a are contained in b. The right-hand concept supplies the complete domain. For example, every order belongs to a customer, but some customers have no orders:

import customers as customers;
import orders as orders;

select
    customers.id,
    customers.name,
    coalesce(sum(orders.total), 0) as total_spend,
subset join orders.customer_id = customers.id
order by total_spend desc;

Customers without orders remain in the result. coalesce displays their missing spend as zero. The operand order matters: put the subset on the left and the complete domain on the right.

Union Join: Compare Two Populations

union join a = b preserves unmatched values from both domains. Use it when neither population contains the other, such as web and store sales.

First aggregate each channel to the same grain, then join the results. These models expose an order id, date, product_id, and total:

import web_orders as web;
import store_orders as store;

rowset web_daily <- select
    web.date as sale_date,
    web.product_id as product_id,
    sum(web.total) as web_sales,
;

rowset store_daily <- select
    store.date as sale_date,
    store.product_id as product_id,
    sum(store.total) as store_sales,
;

select
    web_daily.sale_date,
    web_daily.product_id,
    web_daily.web_sales,
    store_daily.store_sales,
    coalesce(web_daily.web_sales, 0)
        + coalesce(store_daily.store_sales, 0) as combined_sales,
union join web_daily.sale_date = store_daily.sale_date
union join web_daily.product_id = store_daily.product_id
order by web_daily.sale_date asc, web_daily.product_id asc;

The joined keys represent the combined domain, including dates and products found in only one channel. An absent channel's measure is null; the derived combined_sales treats that absence as zero.

Match every component of the shared grain. Here that means both date and product; joining only on date would pair unrelated products. Do not join on sales amounts or assume that order IDs from independent channels identify the same order.

Alias rowset fields that you reference later, as above. See Rowset Statements for staging reusable query results.

Expression Keys: Compare Years

Join keys can be expressions. These two rowsets summarize the same model by year, then pair each year with its predecessor:

import orders as orders;

rowset current_year <- select
    year(orders.date) as sales_year,
    sum(orders.total) as sales,
;

rowset prior_year <- select
    year(orders.date) as sales_year,
    sum(orders.total) as sales,
;

select
    current_year.sales_year,
    current_year.sales as current_sales,
    prior_year.sales as prior_sales,
union join current_year.sales_year = prior_year.sales_year + 1
order by current_year.sales_year asc;

The union join retains years with no matching counterpart. For a comparison that requires data on both sides, add an explicit output filter.

Filter the Joined Result

Joins preserve rows by default. Use HAVING to require both populations after joining. With the daily rowsets above:

select
    web_daily.sale_date,
    web_daily.product_id,
    web_daily.web_sales,
    store_daily.store_sales,
union join web_daily.sale_date = store_daily.sale_date
union join web_daily.product_id = store_daily.product_id
having web_daily.web_sales is not null
    and store_daily.store_sales is not null
order by web_daily.sale_date asc, web_daily.product_id asc;

This keeps date/product pairs with non-null sales in both channels. If a measure can be null even when a row exists, project a presence count in each rowset and filter on those counts instead.

Choose a Relationship or a Row Operation

Goal	Construct
Enrich a complete domain with a subset for one query	subset join
Compare populations while retaining unmatched keys	union join
Stack rows from independent queries into shared columns	union(...)
Define a reusable relationship in the model	Model-level merge
Stage filtered or aggregated results before joining	Named rowsets

union join matches keys and combines columns. union(...) stacks rows by column position, preserving duplicates like SQL UNION ALL.

See Query Examples for more patterns, including membership and anti-joins.