# Query syntax

[Read this page in HTML](https://www.scopedb.io/docs/reference/commands/stmt-query)

ScopeQL queries are pipelines. Each clause takes the rows produced by the preceding clause, so you can filter a calculated column or aggregate a filtered result without wrapping it in a subquery.

Parenthesized queries can also start a pipeline, as in the [`UNION ALL` example](https://www.scopedb.io/docs/reference/commands/query-operators/set.md#examples).

The outline below shows a common clause order. Clauses such as `SELECT` and `WHERE` can appear more than once.

```scopeql
[ FROM ... [ SAMPLE ... PERCENT ] | VALUES ... ]
[ [ INNER | LEFT | RIGHT | FULL ] JOIN ... [ ON ... ] ]
[ SELECT ... ]
[ WHERE ... ]
[ DISTINCT ON ... [ BY ... ] ]
[ [ GROUP BY ... ] [ WITHIN GROUP ORDER BY ... ] WINDOW ... ]
[ [ GROUP BY ... ] [ WITHIN GROUP ORDER BY ... ] AGGREGATE ... ]
[ ORDER BY ... ]
[ LIMIT ... [ OFFSET ... ] ]
```

## Sample data

The table examples on this page use four request events. Run these two statements once before trying them:

```scopeql
CREATE TABLE reference_requests (
    event_id string,
    event_time timestamp,
    service string,
    status int,
    latency_ms int
)
```

```scopeql
VALUES
    ('req-1', '2026-06-01T10:00:00Z'::timestamp, 'checkout', 200, 120),
    ('req-2', '2026-06-01T10:00:10Z'::timestamp, 'checkout', 504, 1800),
    ('req-3', '2026-06-01T10:00:20Z'::timestamp, 'catalog', 200, 40),
    ('req-4', '2026-06-01T10:00:30Z'::timestamp, 'checkout', 200, 180)
INSERT INTO reference_requests
```

Examples starting with `VALUES` include their own data and need no table setup.

## FROM

`FROM` reads rows from a table or view. Use `schema.table` to qualify a table name and `AS` to give the source an alias.

```scopeql
FROM reference_requests AS requests
SELECT requests.event_id AS event_id, requests.service AS service
ORDER BY event_id
```

```text
+----------+----------+
| event_id | service  |
+----------+----------+
| req-1    | checkout |
| req-2    | checkout |
| req-3    | catalog  |
| req-4    | checkout |
+----------+----------+
```

`FROM reference_requests` by itself returns all columns. Without `ORDER BY`, the row order is not guaranteed.

## VALUES

`VALUES` supplies rows directly in a query. Columns are named `$0`, `$1`, and so on, counting from 0. Use `SELECT` to assign meaningful names:

```scopeql
VALUES ('checkout', 120), ('catalog', 40)
SELECT $0 AS service, $1 AS latency_ms
```

```text
+----------+------------+
| service  | latency_ms |
+----------+------------+
| checkout | 120        |
| catalog  | 40         |
+----------+------------+
```

The first row determines each column’s type. If the first value is NULL, give it a type, such as `NULL::string`. Every row must have the same number of values.

## SELECT

`SELECT` chooses, renames, or calculates columns. Later clauses can use the names assigned by `AS`.

Convert request latency from milliseconds to seconds:

```scopeql
FROM reference_requests
SELECT event_id, latency_ms / 1000.0 AS latency_seconds
ORDER BY event_id
```

```text
+----------+-----------------+
| event_id | latency_seconds |
+----------+-----------------+
| req-1    | 0.12            |
| req-2    | 1.8             |
| req-3    | 0.04            |
| req-4    | 0.18            |
+----------+-----------------+
```

Use `*` to keep all current columns, including when adding a calculated column:

```scopeql
VALUES ('req-1', 120), ('req-2', 1800)
SELECT $0 AS event_id, $1 AS latency_ms
SELECT *, latency_ms >= 1000 AS slow
```

```text
+----------+------------+-------+
| event_id | latency_ms | slow  |
+----------+------------+-------+
| req-1    | 120        | false |
| req-2    | 1800       | true  |
+----------+------------+-------+
```

You can select a column by its zero-based position, such as `SELECT $0`. A `SELECT` without an input source can evaluate expressions directly, for example `SELECT now() AS query_time`.

## WHERE

`WHERE <predicate>` keeps rows for which the predicate is TRUE. FALSE and NULL both exclude a row. Combine conditions with [logical operators](https://www.scopedb.io/docs/reference/commands/query-operators/logical.md).

Find checkout requests that returned a server error:

```scopeql
FROM reference_requests
WHERE service = 'checkout' AND status >= 500
SELECT event_id, status, latency_ms
ORDER BY event_id
```

```text
+----------+--------+------------+
| event_id | status | latency_ms |
+----------+--------+------------+
| req-2    | 504    | 1800       |
+----------+--------+------------+
```

Place `WHERE` after `SELECT`, `AGGREGATE`, or `WINDOW` to filter their results. `WHERE` also limits the rows affected by [`UPDATE`](https://www.scopedb.io/docs/reference/commands/statements/update.md) and [`DELETE`](https://www.scopedb.io/docs/reference/commands/statements/delete.md).

## JOIN

`JOIN` combines the current rows with a table, view, or subquery. The clauses before `JOIN` form its left side.

### Syntax

```scopeql
[ INNER | LEFT | RIGHT | FULL ] JOIN <right_source> [ AS <alias> ] [ ON <predicate> ]
```

| Join type | Rows returned                                                         |
| --------- | --------------------------------------------------------------------- |
| `INNER`   | Matching rows from both sides. This is the default.                   |
| `LEFT`    | All left rows, with NULL right columns when no match exists.          |
| `RIGHT`   | All right rows, with NULL left columns when no match exists.          |
| `FULL`    | Matches and unmatched rows from both sides; missing columns are NULL. |

Use aliases to distinguish columns with the same name. If `ON` is omitted, every left row is paired with every right row. Multiple matching rows produce multiple output rows.

### Example

Add a responsible team to each request. The service metadata here contains only checkout, so a left join keeps the catalog request with a NULL team:

```scopeql
FROM reference_requests AS requests
LEFT JOIN (
    VALUES ('checkout', 'payments')
    SELECT $0 AS service, $1 AS team
) AS services ON requests.service = services.service
SELECT requests.event_id AS event_id, requests.service AS service, services.team AS team
ORDER BY event_id
```

```text
+----------+----------+----------+
| event_id | service  | team     |
+----------+----------+----------+
| req-1    | checkout | payments |
| req-2    | checkout | payments |
| req-3    | catalog  | NULL     |
| req-4    | checkout | payments |
+----------+----------+----------+
```

Change `LEFT JOIN` to `JOIN` to keep only requests with a matching team. You can add another `JOIN` after any clause that produces rows.

## AGGREGATE

`AGGREGATE` calculates summary values. Without `GROUP BY`, it returns one row for the entire input. With `GROUP BY`, it returns one row per combination of group keys.

### Syntax

```scopeql
[ GROUP BY <expr> [ AS <alias> ] [, ...] ]
[ WITHIN GROUP ORDER BY <order_item> [, ...] ]
AGGREGATE <function_call> [ AS <alias> ] [, ...]
```

Count requests and calculate average latency for each service:

```scopeql
FROM reference_requests
GROUP BY service
AGGREGATE count() AS requests, avg(latency_ms) AS avg_ms
ORDER BY service
```

```text
+----------+----------+--------+
| service  | requests | avg_ms |
+----------+----------+--------+
| catalog  | 1        | 40.0   |
| checkout | 3        | 700.0  |
+----------+----------+--------+
```

Add group keys to split the result further, or omit `GROUP BY` for a total. For example, group requests by service and status:

```scopeql
FROM reference_requests
GROUP BY service, status
AGGREGATE count() AS requests
ORDER BY service, status
```

```text
+----------+--------+----------+
| service  | status | requests |
+----------+--------+----------+
| catalog  | 200    | 1        |
| checkout | 200    | 2        |
| checkout | 504    | 1        |
+----------+--------+----------+
```

For order-sensitive aggregates such as `first`, `last`, and `array_agg`, use `WITHIN GROUP ORDER BY` before `AGGREGATE`. An `ORDER BY` after the aggregation sorts the result rows. See [aggregate functions](https://www.scopedb.io/docs/reference/functions/aggregate.md) for empty-input and NULL behavior.

## WINDOW

`WINDOW` calculates a value for each input row while keeping the original columns. `GROUP BY` creates independent windows; without it, all input rows belong to one window. `WITHIN GROUP ORDER BY` sets the row order inside each window.

### Syntax

```scopeql
[ GROUP BY <expr> [, ...] ]
[ WITHIN GROUP ORDER BY <order_item> [, ...] ]
WINDOW <function_call> [ AS <alias> ] [, ...]
```

Measure the time between consecutive requests to each service:

```scopeql
FROM reference_requests
GROUP BY service
WITHIN GROUP ORDER BY event_time, event_id
WINDOW lag(event_time) AS previous_time
SELECT event_id, service, event_time - previous_time AS since_previous
ORDER BY event_id
```

```text
+----------+----------+----------------+
| event_id | service  | since_previous |
+----------+----------+----------------+
| req-1    | checkout | NULL           |
| req-2    | checkout | 10s            |
| req-3    | catalog  | NULL           |
| req-4    | checkout | 20s            |
+----------+----------+----------------+
```

The first request in each service has no previous timestamp, so its interval is NULL. See [window functions](https://www.scopedb.io/docs/reference/functions/window.md) for `lag`, `lead`, and `row_number`.

## DISTINCT ON

`DISTINCT ON` keeps one row for each combination of key values. Use `BY` to specify which row to keep. Without `BY`, or when its sort keys tie, the selected row is not guaranteed.

### Syntax

```scopeql
DISTINCT ON <expr> [, ...]
[ BY <order_item> [, ...] ]
```

Each `BY` item uses the same syntax as an [`ORDER BY`](#order-by) item. `BY` chooses a row within each group; a separate `ORDER BY` sorts the final result.

Keep the latest request for each service, using the event ID to break timestamp ties:

```scopeql
FROM reference_requests
DISTINCT ON service BY event_time DESC, event_id DESC
SELECT event_id, service, status
ORDER BY service
```

```text
+----------+----------+--------+
| event_id | service  | status |
+----------+----------+--------+
| req-3    | catalog  | 200    |
| req-4    | checkout | 200    |
+----------+----------+--------+
```

To remove duplicate combinations without choosing between other fields, select only the keys:

```scopeql
FROM reference_requests
SELECT service, status
DISTINCT ON service, status
ORDER BY service, status
```

```text
+----------+--------+
| service  | status |
+----------+--------+
| catalog  | 200    |
| checkout | 200    |
| checkout | 504    |
+----------+--------+
```

## ORDER BY

`ORDER BY` sorts the current rows by one or more expressions. Use column names, aliases from an earlier clause, or positional references such as `$0`.

### Syntax

```scopeql
ORDER BY <expr> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, ...]
```

* `ASC` sorts from lowest to highest and is the default. NULL values come last.
* `DESC` sorts from highest to lowest. NULL values come first.
* `NULLS FIRST` or `NULLS LAST` overrides the default NULL placement.
* Rows with equal sort keys have no guaranteed order. Add another key when the order matters.

List the slowest requests first, placing missing durations last:

```scopeql
VALUES ('req-1', 120), ('req-2', NULL), ('req-3', 1800)
SELECT $0 AS event_id, $1 AS latency_ms
ORDER BY latency_ms DESC NULLS LAST, event_id
```

```text
+----------+------------+
| event_id | latency_ms |
+----------+------------+
| req-3    | 1800       |
| req-1    | 120        |
| req-2    | NULL       |
+----------+------------+
```

## LIMIT

`LIMIT` returns at most the requested number of rows. `OFFSET` skips rows before taking that limit; it defaults to 0. Both values must be non-negative integer constants.

### Syntax

```scopeql
LIMIT <count> [ OFFSET <start> ]
```

Find the two slowest requests:

```scopeql
FROM reference_requests
ORDER BY latency_ms DESC, event_id
LIMIT 2
SELECT event_id, latency_ms
```

```text
+----------+------------+
| event_id | latency_ms |
+----------+------------+
| req-2    | 1800       |
| req-4    | 180        |
+----------+------------+
```

Use `LIMIT 2 OFFSET 2` for the next two rows. Put `ORDER BY` before `LIMIT` to define which rows are selected.

## SAMPLE

`SAMPLE` randomly includes rows from a table with a given probability. The percentage can range from 0 to 100, inclusive. The returned count can vary; it is not an exact percentage of the row count.

### Syntax

```scopeql
FROM <table_name> SAMPLE <probability> PERCENT
```

Sample approximately 10% of request events:

```scopeql
FROM reference_requests SAMPLE 10 PERCENT
```

On this four-row table, the sample may be empty. A larger table makes sampling more useful for inspecting representative rows.