Multi-Select Statements

Multi-select statements combine multiple select queries using merge and align operations. This enables UNION-like operations with proper concept alignment.

Syntax

multi_select_statement: select_statement ("merge" select_statement)+
    "align" align_clause
    ("derive" derive_clause)?
    where? order_by? limit?

Align Clause

The align clause specifies which concepts map to each other across the merged selects:

align_clause: align_item ("and" align_item)*
align_item: IDENTIFIER ":" IDENTIFIER ("," IDENTIFIER)*

Basic Multi-Select

Merge two queries and align their concepts:

select
    web_order.id,
    web_order.total,
    web_order.date
merge
select
    store_order.id,
    store_order.total,
    store_order.date
align
    order_id: web_order.id, store_order.id
    and order_total: web_order.total, store_order.total
    and order_date: web_order.date, store_order.date
;

Derive Clause

Create new concepts from the aligned results:

select
    web_order.id,
    web_order.total
merge
select
    store_order.id,
    store_order.total
align
    order_id: web_order.id, store_order.id
    and order_total: web_order.total, store_order.total
derive
    order_total * 1.1 -> total_with_tax
;

With Filtering and Ordering

Apply where, order by, and limit to the merged result:

select
    online_sale.product_id,
    online_sale.quantity
merge
select
    retail_sale.product_id,
    retail_sale.quantity
align
    product: online_sale.product_id, retail_sale.product_id
    and qty: online_sale.quantity, retail_sale.quantity
where
    qty > 0
order by
    qty desc
limit 100
;

Use Cases

• Combining data from different sources
• UNION operations with semantic alignment
• Cross-channel reporting
• Consolidating disparate schemas

Example

import web_orders as web;
import store_orders as store;
select
    web.order.id,
    web.order.customer_name,
    web.order.total,
    'web' -> channel
merge
select
    store.order.id,
    store.order.customer_name,
    store.order.total,
    'store' -> channel
align
    order_id: web.order.id, store.order.id
    and customer: web.order.customer_name, store.order.customer_name
    and amount: web.order.total, store.order.total
order by
    amount desc
;