> For the complete documentation index, see [llms.txt](https://upsolver.gitbook.io/content/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://upsolver.gitbook.io/content/reference-1/sql-commands/materialized-views/join-with-a-materialized-view.md).

# Join with a Materialized View

Materialized views can be joined with other Upsolver tables to perform a lookup operation that returns the values corresponding to the given key column values.

## Syntax

```sql
SELECT ...
 FROM <table> [ [AS] <table_alias> ]
 LEFT [OUTER] JOIN [ARRAY] <mv_name> [ [AS] <alias> ]
 AS OF <time_expression>]
 ON { <alias> | <mv_name> }.<column> = { <table> | <table_alias> }.<column> [, ...]
```

#### Jump to

* [`ARRAY` keyword](#array-keyword)
* [`AS OF` statement](#as-of)
* [`ON` statement](#on-statement)

### `ARRAY` keyword

This keyword should be used when the materialized view has a key column that is an array.

Note that this uses the array itself as the key. To use the items within the arrays as the key column, you should first flatten your data by writing to a new table using [`UNNEST`](/content/reference-1/sql-commands/jobs/create-job/transformation/insert/unnest.md) with an [`INSERT`](/content/reference-1/sql-commands/jobs/create-job/transformation/insert.md) job.

### `AS OF` statement

AS OF is relevant when the business case requires time synchronization between the data in the main table and the materialized view. It allows users to define the point in time at which the materialized view is joined with the main table. It lets you create a delay in pipeline processing or join with a state at a specific point in time of the materialized view.

Value: `AS OF time_expression`

`AS OF` is optional. In case it isn't included, the main table will always join the latest available data in the materialized view. It is recommended to use `AS OF` when using a `SYNC` job to join with the materialized view.

**When to use AS OF:**

You want to link a click with an ad impression. Business wise the pipeline needs to process the click after the impression is added to the materialized view. Although both streams are expected to arrive in the right order in most cases, you can ensure it by delaying the click processing by a few minutes. Use `AS OF $event_time + INTERVAL '5' MINUTE`. When selecting an interval value, keep in mind that it will cause a delay in your pipeline data freshness.

You want to join the main table with the data that was available at the end of 2022 in the materialized view. Use `AS OF timestamp '2023-01-01 00:00:00'`

### `ON` statement

The `ON` statement defines the columns on which the materialized view is joined with the main table.&#x20;

All key columns of your materialized view should be included in this section.

## Example

```sql
CREATE SYNC JOB join_two_tables_orders_with_last_employee
    START_FROM = BEGINNING
    ADD_MISSING_COLUMNS = TRUE
    RUN_INTERVAL = 1 MINUTE
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data 
  MAP_COLUMNS_BY_NAME
    SELECT 
       orderid, 
       mv.employeeid as employeeid, 
       mv.firstname as firstname, 
       mv.lastname as lastname
    FROM default_glue_catalog.upsolver_samples.orders_raw_data
    LEFT JOIN 
 default_glue_catalog.upsolver_samples.physical_store_orders_materialized_view AS mv
      AS OF $event_time + INTERVAL '5' MINUTE
      ON mv.orderid = orderid
    WHERE mv.source = 'Store'
    AND time_filter();
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://upsolver.gitbook.io/content/reference-1/sql-commands/materialized-views/join-with-a-materialized-view.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.