M↓ View as Markdown ↗

Core Syntax¶

SemQL uses familiar, portable SQL syntax. If you know standard SQL, you already know most of SemQL. The differences are:

What you reference: semantic Model, Dimension and Measure names instead of physical tables and columns.
Portability: warehouse-specific syntax is not accepted. DISTINCT ON, ~ regex matching, = ANY(ARRAY[…]), LATERAL, TOP n and $1 placeholders are rejected with an error that names the portable alternative, for example ROW_NUMBER() in a CTE, REGEXP_LIKE, IN (…) or LIMIT n.

Some advanced constructs aren't available on every database; see Compilation & Dialects for the per-database list.

SELECT¶

Select the fields you want in the output. These can be Dimensions, Measures, expressions and literals.

-- Dimensions (attributes you slice by)
SELECT name, region, created_at FROM customers

-- Measures (already-aggregated metrics)
SELECT total_revenue, order_count FROM sales

-- With a Model qualifier and aliases (use when field names are ambiguous)
SELECT organizations.name AS organization_name, users.name AS user_name
FROM organizations
JOIN users ON organizations.organization_id = users.organization_id

-- Aliases
SELECT name AS organization_name, total_revenue AS revenue FROM organizations

-- Literals
SELECT name, 'active' AS status FROM organizations[active_only]

-- Distinct values
SELECT DISTINCT region FROM customers

When to qualify field names: you only need to qualify a field (for example organizations.name) when the same field name exists in more than one Model in the query. When it's unambiguous, use the bare field name.

Output column names must be unique. Two selected columns can't share a name. Alias columns that share a field name, as in the join example above. Otherwise SemQL rejects the query with Output column "name" appears more than once.

Names and case: unquoted Model and field names match regardless of case. Quoted names, such as "Region", must match exactly. Aliases can be quoted too: AS "Total revenue". When a name isn't found, the error suggests the closest names.

SELECT *¶

SELECT * expands to the Dimensions of the Model that you are allowed to see. Measures are not included.

SELECT * FROM orders
SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.customer_id

Across a join, Dimension names that appear in more than one source are prefixed with the source's alias (or the Model name when there is no alias), for example orders_region and customers_region, or o_region and c_region when you alias the Models. The query AI Analyst keeps lists the expanded columns explicitly, so re-running it later returns the same columns even if Dimensions are added to the Model.

FROM¶

Reference a semantic Model by name, without a schema prefix or physical table path.

FROM organizations
FROM sales
FROM customers c        -- with an alias

Every SELECT needs a FROM that names a Model, including inside CTEs and subqueries. A query without FROM is refused.

Applying predefined Filters¶

Apply reusable Filter conditions in square brackets after the Model name:

FROM organizations[active_only]
FROM sales[last_quarter]
FROM sales[last_quarter, high_value]   -- multiple Filters, all applied

Filters can also appear in JOIN clauses:

FROM sales
JOIN customers[enterprise] ON sales.customer_id = customers.customer_id

If a Filter name doesn't exist on the Model, the query is refused and the error suggests the closest Filter name.

Note

Filters go in FROM or JOIN, never in SELECT. Filters with Apply to every query turned on are applied automatically. See Dimensions, Measures & Filters.

Derived tables¶

You can use a subquery in FROM or JOIN. It needs an alias:

SELECT region, revenue
FROM (
    SELECT region, total_revenue AS revenue
    FROM sales
) AS by_region
WHERE revenue > 100000

WHERE¶

Filter rows on Dimension values, before any aggregation happens.

SELECT region, total_revenue
FROM sales
WHERE region = 'Europe'

SELECT product_name, order_count
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
  AND status = 'completed'

Measures, aggregate functions and window functions can't be used in WHERE.

WHERE vs. HAVING: use WHERE to filter Dimensions (raw values). Use HAVING to filter Measures (aggregated results). See Dimensions, Measures & Filters.

GROUP BY¶

Group results by one or more Dimensions. GROUP BY is optional.

Implicit grouping: if a query aggregates (uses a Measure or an aggregate function) and has no GROUP BY, SemQL groups by every non-aggregated expression in SELECT, exactly as written. Literals and window functions are never used as grouping keys.
GROUP BY ALL does the same as leaving GROUP BY out.
Explicit GROUP BY: if you write GROUP BY yourself, every Dimension used outside an aggregate in SELECT must be grouped. You can group by name, by alias or by position (GROUP BY 1, 2). SemQL doesn't fill in missing columns; it rejects the query instead.

-- Implicit grouping: grouped by region
SELECT region, total_revenue
FROM sales

-- Explicit grouping
SELECT year, quarter, product_category, total_revenue, order_count
FROM sales
GROUP BY year, quarter, product_category
ORDER BY year, quarter

-- Grouping by position
SELECT region, DATE_TRUNC('month', order_date) AS month, total_revenue
FROM orders
GROUP BY 1, 2

-- An expression built only from grouped Dimensions is fine
SELECT UPPER(region) AS region_label, total_revenue
FROM orders
GROUP BY region

Measures can't be used in GROUP BY.

ROLLUP, CUBE and GROUPING SETS¶

You can write ROLLUP, CUBE and GROUPING SETS as an explicit GROUP BY. Some databases don't support them; see Compilation & Dialects.

SELECT region, product_category, total_revenue
FROM sales
GROUP BY ROLLUP (region, product_category)

HAVING¶

Filter groups after aggregation. Use this to filter on Measure values.

SELECT region, total_revenue
FROM sales
GROUP BY region
HAVING total_revenue > 100000

SELECT customer_id, order_count
FROM orders
GROUP BY customer_id
HAVING order_count >= 5
ORDER BY order_count DESC

ORDER BY, LIMIT and OFFSET¶

SELECT name, total_revenue
FROM organizations
GROUP BY name
ORDER BY total_revenue DESC
LIMIT 10

-- Ascending order (default), with NULLs last
SELECT name, created_at FROM customers ORDER BY created_at ASC NULLS LAST

-- Skip rows
SELECT name FROM customers ORDER BY name LIMIT 10 OFFSET 20

-- Another spelling of LIMIT
SELECT name FROM customers ORDER BY name FETCH FIRST 10 ROWS ONLY

OFFSET isn't available on Azure Synapse or ZenDB.

JOIN¶

Supported joins: JOIN / INNER JOIN, LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN, FULL [OUTER] JOIN, CROSS JOIN and comma joins, with ON or USING.

-- Inner join
SELECT organizations.name, users.email
FROM organizations
JOIN users ON organizations.organization_id = users.organization_id

-- Left join (keep all rows from the left Model)
SELECT organizations.name, COUNT(users.user_id) AS user_count
FROM organizations
LEFT JOIN users ON organizations.organization_id = users.organization_id
GROUP BY organizations.name

-- USING: the join column appears once in the output
SELECT order_id, name
FROM orders
JOIN customers USING (customer_id)

-- With Filters on joined Models
SELECT organizations.name, users.email
FROM organizations[active_only]
JOIN users[verified] ON organizations.organization_id = users.organization_id

Keep these rules in mind when joining:

One data source. All Models in a query must come from the same data connection.
Filters on outer joins. A Filter on the optional side of an outer join (for example LEFT JOIN users[verified]) restricts that Model's rows before the join. Rows from the other side are kept even when they have no match.
Fan-out. If a join repeats a Model's rows, SUM, COUNT and AVG over that Model are refused. See Joins and fan-out.
FULL JOIN isn't available on ZenDB.

Tip

Check the relationships defined in your semantic layer for the correct join columns rather than guessing field names. See Relationships.

WITH (CTEs)¶

Common Table Expressions let you break a complex query into named steps. Standard SQL WITH syntax works.

WITH revenue_by_region AS (
    SELECT region, total_revenue
    FROM sales
    WHERE year = 2024
    GROUP BY region
),
top_regions AS (
    SELECT region, total_revenue
    FROM revenue_by_region
    WHERE total_revenue > 500000
)
SELECT region, total_revenue
FROM top_regions
ORDER BY total_revenue DESC

Recursive CTEs (WITH RECURSIVE … UNION) are also supported.

Note

Inside a CTE body, you write against semantic Models as usual. Outside it, references to the CTE use its output columns: total_revenue in top_regions is a plain column, not a Measure, so you can aggregate it (for example SUM(total_revenue)).

ZenDB

ZenDB data sources don't support WITH. SemQL returns an "Unsupported" error; rewrite the CTE as an inline subquery.

UNION, INTERSECT and EXCEPT¶

Combine the results of several queries. Each part must return the same number of columns with compatible types. Each part groups and filters on its own; ORDER BY and LIMIT at the end apply to the combined rows.

-- UNION removes duplicates; UNION ALL keeps them
SELECT 'Q1' AS quarter, total_revenue FROM sales WHERE quarter = 1
UNION ALL
SELECT 'Q2' AS quarter, total_revenue FROM sales WHERE quarter = 2

-- Comparing two segments side by side
SELECT 'Enterprise' AS segment, customer_count FROM customers[enterprise]
UNION ALL
SELECT 'SMB' AS segment, customer_count FROM customers[smb]

-- Customers who ordered in 2024 but not in 2025
SELECT customer_id FROM orders WHERE year = 2024
EXCEPT
SELECT customer_id FROM orders WHERE year = 2025

INTERSECT and EXCEPT aren't available on ZenDB. INTERSECT ALL and EXCEPT ALL aren't available on several databases; see Compilation & Dialects.

Window functions¶

Window functions compute running totals, rankings and period-over-period comparisons without collapsing rows into groups. You can use a Measure inside a window aggregate, for example SUM(total_revenue) OVER (…).

Database support

Window functions aren't available on ZenDB. On Actian, Ingres and Informix, aggregate windows with ORDER BY inside OVER (such as the running total below) are rejected. Ranking functions and windows without ORDER BY still work there.

Ranking¶

SELECT
    region,
    total_revenue,
    ROW_NUMBER() OVER (ORDER BY total_revenue DESC) AS rank,
    RANK() OVER (ORDER BY total_revenue DESC) AS rank_with_ties,
    DENSE_RANK() OVER (ORDER BY total_revenue DESC) AS dense_rank
FROM sales
GROUP BY region

Running totals¶

SELECT
    order_date,
    total_revenue,
    SUM(total_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders
GROUP BY order_date
ORDER BY order_date

Period-over-period comparisons¶

SELECT
    order_date,
    total_revenue,
    LAG(total_revenue) OVER (ORDER BY order_date) AS prev_period_revenue,
    total_revenue - LAG(total_revenue) OVER (ORDER BY order_date) AS change
FROM orders
GROUP BY order_date
ORDER BY order_date

Partitioned windows¶

SELECT
    region,
    product_category,
    total_revenue,
    ROW_NUMBER() OVER (
        PARTITION BY region
        ORDER BY total_revenue DESC
    ) AS rank_within_region
FROM sales
GROUP BY region, product_category

A window over an aggregate gives a grand total next to each group:

SELECT
    region,
    COUNT(*) AS orders_placed,
    SUM(COUNT(*)) OVER () AS all_orders
FROM orders

Window functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, CUME_DIST, LEAD, LAG, FIRST_VALUE, LAST_VALUE, NTH_VALUE, and aggregate functions with OVER. Window frames use ROWS or RANGE.

Subqueries¶

Subqueries work in IN, EXISTS and as scalar values. They can be correlated: a column the subquery doesn't know is resolved against the enclosing query (plain Dimensions only).

SELECT name, total_revenue
FROM organizations
WHERE organization_id IN (
    SELECT organization_id
    FROM orders
    WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY organization_id
    HAVING COUNT(*) >= 3
)
GROUP BY name

All Models in subqueries must come from the same data connection as the rest of the query.

Expressions¶

Syntax	Example
Arithmetic and comparison	`amount * 1.2`, `a <> b`, `a != b`
`CASE`	`CASE WHEN amount > 100 THEN 'large' ELSE 'small' END`
`IN`, `BETWEEN`, `IS [NOT] NULL`	`region IN ('EU', 'US')`, `amount BETWEEN 10 AND 20`
`IS [NOT] DISTINCT FROM`	`status IS DISTINCT FROM 'cancelled'`
`LIKE` / `ILIKE`, with `ESCAPE`	`name ILIKE 'acme%'`, `code LIKE '50!%%' ESCAPE '!'`
String concatenation	`first_name \|\| ' ' \|\| last_name`
Typed literals	`DATE '2024-01-01'`, `TIMESTAMP '2024-01-01 09:00:00'`
Intervals	`CURRENT_DATE - INTERVAL '30 days'`
Casts	`CAST(amount AS integer)`, `amount::numeric(10,2)`
Conditional aggregates	`COUNT(*) FILTER (WHERE status = 'completed')`
Comments	`-- line comment`, `/* block comment */`

Casts¶

CAST(x AS type) and x::type accept these portable types: text, integer, bigint, numeric(p,s), double, boolean, date, timestamp. Common spellings such as varchar, int, decimal, float and datetime are accepted as aliases. SemQL writes each database's own type name. Any other type is refused with an "Unknown cast type" error.

Type checks¶

SemQL rejects some clear type mistakes before the query reaches your database: arithmetic on non-numeric fields (such as name * 2), SUM or AVG over a non-numeric field, and conditions that aren't true/false (such as WHERE region).

Common functions¶

SemQL translates many common functions into your database's own SQL, including DATE_TRUNC, EXTRACT / DATE_PART, NOW(), CONCAT and ||, SUBSTRING, TRIM, POSITION, REGEXP_LIKE and the geo functions. Other function calls are passed to your database by name, so whether they work depends on your database.

Aggregate¶

Function	Description
`COUNT(x)`, `COUNT(*)`, `COUNT(DISTINCT x)`	Count rows or unique values
`SUM(x)`	Sum of values
`AVG(x)`	Average
`MIN(x)`, `MAX(x)`	Minimum / maximum
`STDDEV(x)`, `VARIANCE(x)`	Standard deviation / variance (also `STDDEV_POP`, `STDDEV_SAMP`, `VAR_POP`, `VAR_SAMP`)
`STRING_AGG(x, sep)`	Concatenate strings with a separator
`ARRAY_AGG(x)`	Aggregate into an array
`BOOL_AND(x)`, `BOOL_OR(x)`	Boolean aggregations
`PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY x)`, `PERCENTILE_DISC(p) WITHIN GROUP (ORDER BY x)`	Percentiles. For a median, use `PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x)`. Available on PostgreSQL, Amazon Redshift, Snowflake, Databricks, MotherDuck and uploaded files; refused with an error on other databases.

Any aggregate can take FILTER (WHERE …) to count or sum only matching rows. On databases without FILTER, SemQL writes the equivalent conditional aggregate.

Warning

Never wrap a Measure in an aggregate function. Measures are already aggregated, and SemQL rejects SUM(total_revenue) with the error Measure total_revenue cannot be wrapped in an aggregate function. Window aggregates such as SUM(total_revenue) OVER (ORDER BY …) are allowed. See Dimensions, Measures & Filters.

Date / time¶

Function	Description
`DATE_TRUNC('month', date)`	Truncate to a time period
`DATE_PART('year', date)`	Extract part of a date
`EXTRACT(YEAR FROM date)`	Extract part of a date
`NOW()`, `CURRENT_DATE`	Current timestamp / date
`CURRENT_TIMESTAMP`	Current timestamp

String¶

Function	Description
`UPPER(x)`, `LOWER(x)`	Change case
`LENGTH(x)`	String length
`TRIM(x)`, `TRIM(LEADING 'x' FROM y)`	Remove whitespace or other characters
`SUBSTRING(x, start, len)`, `SUBSTRING(x FROM start FOR len)`	Extract a substring
`POSITION('a' IN x)`	Position of a substring
`CONCAT(x, y, ...)`, `x \|\| y`	Concatenate strings
`REPLACE(x, from, to)`	Replace a substring
`REGEXP_LIKE(x, pattern)`	True when the pattern matches anywhere in the text. Not available on Microsoft SQL Server, Microsoft Fabric, Azure Synapse, Actian, Ingres, Informix or ZenDB; use `LIKE '%…%'` there.

Math¶

Function	Description
`ABS(x)`	Absolute value
`ROUND(x, n)`	Round to n decimal places
`CEIL(x)`, `FLOOR(x)`	Round up / down
`MOD(x, y)`	Modulo
`POWER(x, y)`, `SQRT(x)`	Exponent / square root
`LEAST(x, y)`, `GREATEST(x, y)`	Minimum / maximum of arguments. Passed to your database as written. Not available on Microsoft SQL Server, Microsoft Fabric, Azure Synapse, Informix or ZenDB; use `CASE` there.

Geo¶

Function	Description
`GEO_DISTANCE(lat1, lng1, lat2, lng2)`	Distance in km between two coordinates
`GEO_WITHIN_RADIUS(lat1, lng1, lat2, lng2, radius_km)`	True/false: is the point within the radius?

See Geo Functions for full documentation.

What SemQL does not support¶

DELETE, UPDATE, INSERT, DROP, TRUNCATE or any other statement that changes data. SemQL is read-only.
Schema-qualified table names such as my_schema.my_table. Use Model names instead.
Direct INFORMATION_SCHEMA queries.
{{ }} templating. Use bare names.
Queries without FROM.
Queries that use Models from more than one data connection.
Warehouse-specific syntax: DISTINCT ON, ~ regex operators, = ANY(ARRAY[…]) and ARRAY[…], LATERAL, TOP n, and $1 bind parameters. The error names the portable form.
Functions on a fixed list of known dangerous functions (for example pg_sleep and dblink).
Nested aggregates such as SUM(SUM(x)), unless the outer one is a window function.
Turning off Filters that have Apply to every query turned on. They always apply.