SELECT

The SELECT statement is the root of a SQL query, and is used both to bind SQL queries to named views or materialized views, and to interactively query data maintained in Materialize. For interactive queries, you should consider creating indexes on the underlying relations based on common query patterns.

Syntax

{{% include-syntax file="examples/select" example="syntax" %}}

Common table expressions (CTEs)

Regular CTEs

{{% include-syntax file="examples/select" example="syntax-with-ctes" %}}

Recursive CTEs

{{% include-syntax file="examples/select" example="syntax-recursive-ctes" %}}

For details and examples, see the Recursive CTEs page.

Details

Because Materialize works very differently from a traditional RDBMS, it's important to understand the implications that certain features of SELECT will have on Materialize.

Creating materialized views

Creating a materialized view generates a persistent dataflow, which has a different performance profile from performing a SELECT in an RDBMS.

A materialized view has resource and latency costs that should be carefully considered depending on its main usage. Materialize must maintain the results of the query in durable storage, but often it must also maintain additional intermediate state.

Creating indexes

Creating an index also generates a persistent dataflow. The difference from a materialized view is that the results are maintained in memory rather than on persistent storage. This allows ad hoc queries to perform efficient point-lookups in indexes.

Ad hoc queries

An ad hoc query (a.k.a. one-off SELECT) simply performs the query once and returns the results. Ad hoc queries can either read from an existing index, or they can start an ephemeral dataflow to compute the results.

Performing a SELECT on an indexed source, view or materialized view is Materialize's ideal operation. When Materialize receives such a SELECT query, it quickly returns the maintained results from memory. Materialize also quickly returns results for queries that only filter, project, transform with scalar functions, and re-order data that is maintained by an index.

Queries that can't simply read out from an index will create an ephemeral dataflow to compute the results. These dataflows are bound to the active cluster, which you can change using:

SET cluster = <cluster name>;

Materialize will remove the dataflow as soon as it has returned the query results to you.

Known limitations

CTEs have the following limitations, which we are working to improve:

• INSERT/UPDATE/DELETE (with RETURNING) is not supported inside a CTE.
• SQL99-compliant WITH RECURSIVE CTEs are not supported (use the non-standard flavor instead).

Query hints

Users can specify query hints to help Materialize optimize queries.

The following query hints are valid within the OPTIONS clause.

For examples, see the Optimization page.

Column references

Within a given SELECT statement, we refer to the columns from the tables in the FROM clause as the input columns, and columns in the SELECT list as the output columns.

Expressions in the SELECT list, WHERE clause, and HAVING clause may refer only to input columns.

Column references in the ORDER BY and DISTINCT ON clauses may be the name of an output column, the ordinal number of an output column, or an arbitrary expression of only input columns. If an unqualified name refers to both an input and output column, ORDER BY chooses the output column.

Column references in the GROUP BY clause may be the name of an output column, the ordinal number of an output column, or an arbitrary expression of only input columns. If an unqualified name refers to both an input and output column, GROUP BY chooses the input column.

Connection pooling

Because Materialize is wire-compatible with PostgreSQL, you can use any PostgreSQL connection pooler with Materialize. For example in using PgBouncer, see Connection Pooling.

Examples

Creating an indexed view

This assumes you've already created a source.

The following query creates a view representing the total of all purchases made by users per region, and then creates an index on this view.

CREATE VIEW purchases_by_region AS
    SELECT region.id, sum(purchase.total)
    FROM mysql_simple_purchase AS purchase
    JOIN mysql_simple_user AS user ON purchase.user_id = user.id
    JOIN mysql_simple_region AS region ON user.region_id = region.id
    GROUP BY region.id;

CREATE INDEX purchases_by_region_idx ON purchases_by_region(id);

In this case, Materialize will create a dataflow to maintain the results of this query, and that dataflow will live on until the index it's maintaining is dropped.

Reading from a view

Assuming you've created the indexed view listed above, named purchases_by_region, you can simply read from the index with an ad hoc SELECT query:

SELECT * FROM purchases_by_region;

In this case, Materialize simply returns the results that the index is maintaining, by reading from memory.

Ad hoc querying

SELECT region.id, sum(purchase.total)
FROM mysql_simple_purchase AS purchase
JOIN mysql_simple_user AS user ON purchase.user_id = user.id
JOIN mysql_simple_region AS region ON user.region_id = region.id
GROUP BY region.id;

In this case, Materialize will spin up a similar dataflow as it did for creating the above indexed view, but it will tear down the dataflow once it's returned its results to the client. If you regularly want to view the results of this query, you may want to create an index (in memory) and/or a materialized view (on persistent storage) for it.

Using regular CTEs

WITH
  regional_sales (region, total_sales) AS (
    SELECT region, sum(amount)
    FROM orders
    GROUP BY region
  ),
  top_regions AS (
    SELECT region
    FROM regional_sales
    ORDER BY total_sales DESC
    LIMIT 5
  )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

Both regional_sales and top_regions are CTEs. You could write a query that produces the same results by replacing references to the CTE with the query it names, but the CTEs make the entire query simpler to understand.

With regard to dataflows, this is similar to ad hoc querying above: Materialize tears down the created dataflow after returning the results.

Privileges

The privileges required to execute this statement are:

{{% include-headless "/headless/sql-command-privileges/select" %}}

Related pages

• CREATE VIEW
• CREATE MATERIALIZED VIEW
• SHOW FULL VIEWS