Select Statements

Select statements are used to output data. They are common for interactive development, reporting, and adhoc-pulls.

Select statements can also define new concepts: an aliased concept in a select is usable throughout the rest of the script.

WHERE?
SELECT <select list>
SUBSET|UNION JOIN?
BY ROLLUP|CUBE|GROUPING SETS?
HAVING?
ORDER BY?
LIMIT?

Where

A where clause can come before the select. The syntax is where followed by any boolean condition.

Where clause conditions apply before any aggregation. Where clause inputs do not need to be in the select list.

Select List

One or more comma-separated concept references or inline transformations bound to name. (Trailing commas are allowed.)

select
#an existing concept
user.id,
# creating a new concept
count(order.id)->user_orders,
# commented columns are resolved in the query but not output
--user.state,
;

Fields can be included without being output by prefixing them with --. This is useful for fields needed only for filtering or sorting. The field remains part of the query; -- is not a comment marker.

Query-Scoped Joins

SUBSET JOIN and UNION JOIN connect concepts for one select without changing the model globally. Place join clauses immediately after the select list.

select
    customers.state,
    count(orders.id) as order_count,
subset join orders.customer_id = customers.id
order by order_count desc;

subset join a = b declares that the values of a are contained in b. union join a = b declares neither domain complete and preserves unmatched values from both. Join fact models on every component of their shared grain.

See Query Examples for single-key, composite-key, and expression-key examples.

Multi-Level Grouping

BY ROLLUP, BY CUBE, and BY GROUPING SETS apply to the entire select and appear before HAVING.

select
    orders.region,
    orders.category,
    sum(orders.revenue) as revenue,
by rollup (orders.region, orders.category)
having revenue > 0;

See ROLLUP, CUBE, and Grouping Sets for grouping levels, subtotal labels, and ranking grouped output.

Having

A having clause can come after the select list. The syntax is having followed by any boolean condition.

Having conditions apply after joins, grouping, aggregation, and window functions. Having clause inputs must be in the select list.

Runtime Behavior

When executed in non-interactive mode, selects will be parsed but no execution will occur.

Example

import part as part;
WHERE part.supplier.nation.region.name = 'EUROPE'
SELECT
	part.supplier.account_balance,
	part.supplier.name,
	part.supplier.nation.name,
	part.id,
	part.manufacturer,
	part.supplier.id,
	part.supplier.address,
	part.supplier.phone,
	part.supplier.comment,
	--part.supply_cost,
	min(part.supply_cost) by part.id as min_part_cost,
HAVING
	part.supply_cost = min_part_cost
ORDER BY
	part.id asc;