---
url: /mosaic/api/sql/queries.md
---
# Queries

SQL query builders.
These utilities build structured representations of queries that are easier to create, manipulate, and analyze.

For example, here is a basic group-by aggregation query that counts the number of records and adds up values by category:

```js
import { Query, count, gt, sum } from "@uwdata/mosaic-sql";

// SELECT "column", count() AS "count", sum("value") AS "value"
//   FROM "table" WHERE "value" > 0 GROUP BY "column"
Query
  .from("table")
  .select("category", { count: count(), sum: sum("value") })
  .groupby("category")
  .where(gt("value", 0))
```

Here is an overview of available methods:

```js
Query
  .with(/* a map of named common table expression queries */)
  .select(/* column names or name -> expression maps */)
  .distinct(/* boolean to denote distinct values only */)
  .distinctOn(/* columns or expressions to deduplicate by */)
  .from(/* source table names or subqueries */)
  .sample(/* number of rows or % to sample */)
  .where(/* filter criteria */)
  .groupby(/* columns or expressions to group by */)
  .having(/* post-aggregation filter criteria */)
  .window(/* named window definitions */)
  .qualify(/* post-window filter criteria */)
  .orderby(/* columns or expressions to sort by */)
  .limit(/* max number of rows */)
  .offset(/* offet number of rows */)
```

To learn more about the anatomy of a query, take a look at the [DuckDB Select statement documentation](https://duckdb.org/docs/sql/statements/select).

## Query

The top-level `Query` class, along with its concrete `SelectQuery` and `SetOperation` subclasses, provide structured representations of SQL queries.
The `Query.pivot()` static method additionally returns `PivotQuery` instances for DuckDB simplified `PIVOT` queries.
Upon string coercion, these objects produce a complete SQL query string.

The following static methods create a new `SelectQuery` and invoke the corresponding method:

* `Query.select()`: See the [`select`](#select) method below.
* `Query.from()`: See the [`from`](#from) method below.

The following static method creates a new `PivotQuery`:

* `Query.pivot(source)`: See the [`PivotQuery`](#pivotquery) section below.

In addition, the following static methods take multiple queries as input and return `SetOperation` instances:

* `Query.union(...queries)`: Union results with de-duplication of rows.
* `Query.unionByName(...queries)`: Union results with de-duplication of rows, combining rows from different tables by name, instead of by position.
* `Query.unionAll(...queries)`: Union results with no de-duplication of rows.
* `Query.unionAllByName(...queries)`: Union results with no de-duplication of rows, combining rows from different tables by name, instead of by position.
* `Query.intersect(...queries)`: Query for distinct rows that are output by both the left and right input queries.
* `Query.intersectAll(...queries)`: Query for all rows that are output by both the left and right input queries using bag semantics, so duplicates are returned.
* `Query.except(...queries)`: Query for distinct rows from the left input query that aren't output by the right input query.
* `Query.exceptAll(...queries)`: Query for all rows from the left input query that aren't output by the right input query using bag semantics, so duplicates are returned.

Common table expressions can be applied via static method

* `Query.with()`: See the [`with`](#with) method below.

Each of the methods described above can also be utilized in conjunction with a WITH clause. For example, `Query.with().select()` results in a `SelectQuery`, whereas `Query.with().union()` will produce a `SetOperation`.

To instead create a query for metadata (column names and types), pass a query to the static `describe` method:

* `Query.describe(query)`: Request a description of the columns that a query will produce, with one row per selected column.

## PivotQuery

`Query.pivot(source)`

Create a DuckDB [`PIVOT`](https://duckdb.org/docs/sql/statements/pivot.html#simplified-pivot-syntax) query using simplified syntax over *source* and return a `PivotQuery`.
The *source* may be a table name string, a table name path such as `["schema", "table"]`, or a SQL node such as a query or [`sql`](./expressions#sql) expression.
Use `PivotQuery` methods to add `ON`, `IN`, `USING`, and `GROUP BY` clauses.

```js
import { Query, sum } from "@uwdata/mosaic-sql";

// PIVOT "sales" ON "year" IN (2020, 2021) USING sum("amount") AS "total" GROUP BY "region"
Query
  .pivot("sales")
  .on("year")
  .in(2020, 2021)
  .using({ total: sum("amount") })
  .groupby("region")
```

This method produces DuckDB's simplified `PIVOT` syntax, not the SQL-standard `SELECT ... FROM dataset PIVOT (...)` form.
DuckDB expects at least one of the `ON`, `USING`, or `GROUP BY` clauses when executing a `PIVOT`.
A `PivotQuery` also supports the shared `Query` methods `with`, `orderby`, `setOrderby`, `limit`, `limitPercent`, and `offset`, and may be passed to `from` as a subquery.

### on

`PivotQuery.on(...expressions)`

Add `ON` expressions whose values determine pivot output columns and return this query instance.
The *expressions* may be column name strings, [`column`](./expressions#column) references, or other SQL expressions.
This method is additive: any previously defined `ON` expressions will still remain.

### in

`PivotQuery.in(...values)`

Add `IN` values associated with the `ON` clause and return this query instance.
The values constrain and order pivot output columns, and are only meaningful when an `ON` clause is present.
Non-node values are rendered as SQL literals, so string values become SQL string literals. At least one value is required.
This method is additive: any previously defined `IN` values will still remain.

### using

`PivotQuery.using(...expressions)`

Add `USING` aggregate expressions that populate pivot output cells and return this query instance.
The *expressions* may be aggregate expressions or maps from aliases to aggregate expressions.
At least one expression is required when calling `using`.
If `using` is not called, the `USING` clause is omitted and DuckDB defaults to `count(*)`.
This method is additive: any previously defined `USING` expressions will still remain.

### groupby

`PivotQuery.groupby(...expressions)`

Add `GROUP BY` expressions that define pivot row groups and return this query instance.
DuckDB's simplified `PIVOT` expects column names in `GROUP BY`.
This method is additive: any previously defined `GROUP BY` expressions will still remain.

### setGroupby

`PivotQuery.setGroupby(...expressions)`

Set `GROUP BY` expressions, replacing any prior pivot group by criteria, and return this query instance.

## isPivotQuery

`isPivotQuery(value)`

Return true if *value* is a `PivotQuery` instance.

## clone

`Query.clone()`

Return a new query that is a shallow copy of the current instance.

## toString

`Query.toString()`

Coerce this query object to a SQL query string.

## select

`SelectQuery.select(...expressions)`

Select columns and return this query instance.
The *expressions* argument may include column name strings, [`column` references](./expressions#column), [`star`](./expressions#star) and [`columns`](./expressions#columns) expressions, and maps from column names to expressions (either as JavaScript `object` values or nested key-value arrays as produced by `Object.entries`).

## from

`SelectQuery.from(...tables)`

Indicate the tables to draw records from and return this query instance.
The *tables* may be table name strings, queries or subquery expressions, and maps from table names to expressions (either as JavaScript `object` values or nested key-value arrays as produced by `Object.entries`).

## with

`Query.with(...expressions)`

Provide a set of named subqueries in the form of [common table expressions (CTEs)](https://duckdb.org/docs/sql/query_syntax/with.html) and return this query instance.
The input *expressions* should consist of one or more maps (as JavaScript `object` values) from subquery names to query expressions and/or CTE instances produced by the `cte` method.

## distinct

`SelectQuery.distinct(value = true)`

Update the query to require `DISTINCT` values and return this query instance.

## distinctOn

`SelectQuery.distinctOn(...expressions)`

Update the query to additionally require `DISTINCT ON` the provided *expressions* and return this query instance.
Only the first row for each distinct combination of *expressions* is retained; use `orderby` to determine which row comes first.
If any `DISTINCT ON` expressions are defined, they take precedence over the `distinct` flag.
This method is additive: any previously defined distinct on expressions will still remain.

## sample

`SelectQuery.sample(size, method)`

Update the query to sample a subset of *rows* and return this query instance.
If *size* is a number between 0 and 1, it is interpreted as a percentage of the full dataset to sample.
Otherwise, it is interpreted as the number of rows to sample.
The *method* argument is a string indicating the sample method, such as `"reservoir"`, `"bernoulli"` and `"system"`.
See the [DuckDB Sample documentation](https://duckdb.org/docs/sql/samples) for more.

## where

`SelectQuery.where(...expressions)`

Update the query to additionally filter by the provided predicate *expressions* and return this query instance.
This method is additive: any previously defined filter criteria will still remain.

## groupby

`SelectQuery.groupby(...expressions)`

Update the query to additionally group by the provided *expressions* and return this query instance.
This method is additive: any previously defined group by criteria will still remain.

## having

`SelectQuery.having(...expressions)`

Update the query to additionally filter aggregate results by the provided predicate *expressions* and return this query instance.
Unlike `where` criteria, which are applied before an aggregation, the `having` criteria are applied to aggregated results.
This method is additive: any previously defined filter criteria will still remain.

## window

`SelectQuery.window(...expressions)`

Update the query with named window frame definitions and return this query instance.
The *expressions* arguments should be JavaScript `object` values that map from window names to window frame definitions.
This method is additive: any previously defined windows will still remain.

## qualify

`SelectQuery.qualify(...expressions)`

Update the query to additionally filter windowed results by the provided predicate *expressions* and return this query instance.
Use this method instead of `where` to filter the results of window operations.
This method is additive: any previously defined filter criteria will still remain.

## orderby

`Query.orderby(...expressions)`

Update the query to additionally order results by the provided *expressions* and return this query instance.
This method is additive: any previously defined sort criteria will still remain.

## limit

`Query.limit(rows)`

Update the query to limit results to the specified number of *rows* and return this query instance.

## offset

`Query.offset(rows)`

Update the query to offset the results by the specified number of *rows* and return this query instance.