Materialize/doc/user/content/transform-data/_index.md

---

title: "Overview" description: "Learn how to efficiently transform data using Materialize SQL." disable_list: true menu: main:

parent: transform-data
weight: 5
identifier: transform-overview

---

With Materialize, you can use SQL to transform, deliver, and act on fast-changing data.

{{% materialize-postgres-compatibility %}}

SELECT statement

To build your transformations, you can SELECT from sources, tables, views, and materialized views.

SELECT [ ALL | DISTINCT [ ON ( col_ref [, ...] ) ] ]
    [ { * | projection_expr [ [ AS ] output_name ] } [, ...] ]
    [ FROM table_expr [ join_expr | , ] ... ]
    [ WHERE condition_expr ]
    [ GROUP BY grouping_expr [, ...] ]
    [ OPTIONS ( option = val[, ...] ) ]
    [ HAVING having_expr ]
    [ ORDER BY projection_expr [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, ...] ]
    [ LIMIT { integer  } [ OFFSET { integer } ] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] { SELECT ...} ]

In Materialize, the SELECT statement supports (among others):

• JOINS (inner, left outer, right outer, full outer, cross) and lateral subqueries

• Common Table Expressions (CTEs) and Recursive CTEs

• Query hints (AGGREGATE INPUT GROUP SIZE, DISTINCT ON INPUT GROUP SIZE, LIMIT INPUT GROUP SIZE)

• SQL functions and operators

For more information, see:

• SELECT reference page

• Query optimization

Views and materialized views

A view represent queries that are saved under a name for reference.

CREATE VIEW my_view_name AS
SELECT ...   ;

In Materialize, you can create indexes on views. When you to create an index on a view, the underlying query is executed and the results are stored in memory within the cluster you create the index. As new data arrives, Materialize incrementally updates the view results.

CREATE INDEX idx_on_my_view ON my_view_name(...) ;

You can also create materialized views. A materialized view is a view whose results are persisted in durable storage. As new data arrives, Materialize incrementally updates the view results.

CREATE MATERIALIZED VIEW my_mat_view_name AS
SELECT ...  ;

You can also create an index on a materialized view to make the results available in memory within the cluster you create the index.

For more information, see:

• Views
• Indexes
• Indexed views vs materialized views

Indexes

In Materialize, indexes represent query results stored in memory within a cluster. By making up-to-date view results available in memory, indexes can help improve performance within the cluster. Indexes can also help optimize query performance.

For more information, see:

• Indexes
• Indexed views vs materialized views
• Indexes: Best practices