> ## Documentation Index
> Fetch the complete documentation index at: https://docs.powersync.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Common Table Expressions (CTEs)

> Reuse common query patterns across Sync Streams using Common Table Expressions (CTEs) to simplify configurations and reduce duplication.

When streams need reusable filtering logic, you can define it once in a <Tooltip tip="A common table expression in SQL is a temporary named result set, derived from a simple query and defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement." cta="Definition" href="https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression">Common Table Expression (CTE)</Tooltip> and reference it in queries. CTEs can be defined at the stream level (scoped to one stream) or at the top level of your sync config (shared across all streams). This keeps stream definitions DRY and makes it easier to maintain. For the supported syntax of the `with` block and CTE rules, see [Supported SQL — CTE and WITH syntax](/sync/supported-sql#cte-and-with-syntax).

## Why Use CTEs

Consider an app where users belong to organizations. Several tables need to filter by the user's organizations:

```yaml theme={null}
# Without CTEs - repetitive and hard to maintain
streams:
  org_projects:
    query: |
      SELECT * FROM projects 
      WHERE org_id IN (SELECT org_id FROM org_members WHERE user_id = auth.user_id())
  
  org_repositories:
    query: |
      SELECT * FROM repositories 
      WHERE org_id IN (SELECT org_id FROM org_members WHERE user_id = auth.user_id())
  
  org_settings:
    query: |
      SELECT * FROM settings 
      WHERE org_id IN (SELECT org_id FROM org_members WHERE user_id = auth.user_id())
```

The same subquery appears three times. You can merge these into one stream and define the logic once as a stream-level CTE:

```yaml theme={null}
# With a CTE and multiple queries
streams:
  org_data:
    with:
      user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
    queries:
      - SELECT * FROM projects WHERE org_id IN user_orgs
      - SELECT * FROM repositories WHERE org_id IN user_orgs
      - SELECT * FROM settings WHERE org_id IN user_orgs
```

If the membership logic changes, you update it in one place. When the streams cannot be merged (for example, because they have different `auto_subscribe` settings or separate subscription parameters), use a [global CTE](#global-ctes) to share the logic across streams instead.

## Stream-Level CTEs

Define CTEs in a `with` block inside a stream. Each CTE has a name and a `SELECT` query, and is available only within that stream:

```yaml theme={null}
streams:
  my_stream:
    with:
      cte_name: SELECT columns FROM table WHERE conditions
    query: SELECT * FROM some_table WHERE col IN cte_name
```

The CTE query can include any filtering logic, including parameters:

```yaml theme={null}
streams:
  user_data:
    with:
      user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
      active_projects: SELECT id FROM projects WHERE archived = false
    query: SELECT * FROM projects WHERE org_id IN user_orgs AND id IN active_projects
```

## Global CTEs

When the same filtering logic is needed across streams that cannot be merged — for example because they have different `auto_subscribe` settings or different subscription parameters — define the CTE once in a top-level `with` block. All streams can then reference it without repeating the definition:

```yaml theme={null}
config:
  edition: 3

with:
  user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()

streams:
  # Auto-synced overview data
  org_overview:
    auto_subscribe: true
    queries:
      - SELECT * FROM organizations WHERE id IN user_orgs
      - SELECT * FROM team_members WHERE org_id IN user_orgs

  # On-demand project detail, subscribed per project
  project_detail:
    queries:
      - SELECT * FROM projects WHERE id = subscription.parameter('project_id') AND org_id IN user_orgs
      - SELECT * FROM tasks WHERE project_id = subscription.parameter('project_id')
```

If the membership logic changes, you update it in one place and all streams pick up the change.

A stream can still define its own local `with` block. Local CTEs take precedence over global CTEs when names conflict, so you can override a global CTE for a specific stream:

```yaml theme={null}
config:
  edition: 3

with:
  user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()

streams:
  admin_projects:
    # Local CTE overrides the global one for this stream
    with:
      user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id() AND role = 'admin'
    query: SELECT * FROM projects WHERE org_id IN user_orgs
```

## Using CTEs in Queries

Once defined — either in a stream's local `with` block or in the top-level global `with` block — use the CTE name in the stream's query or queries. You can use it like a subquery or join it as if it were a table.

**Short-hand syntax** (when the CTE has exactly one column):

```yaml theme={null}
streams:
  projects:
    with:
      user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
    query: SELECT * FROM projects WHERE org_id IN user_orgs
```

The short-hand `IN cte_name` is equivalent to `IN (SELECT * FROM cte_name)`. If the CTE has more than one column, this form is an error; use explicit subquery or join syntax instead.

**Explicit subquery syntax** (when you need to select specific columns):

```yaml theme={null}
streams:
  projects:
    with:
      user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
    query: SELECT * FROM projects WHERE org_id IN (SELECT org_id FROM user_orgs)
```

**Join syntax** (you can join a CTE as if it were a table). Only `INNER JOIN` is supported:

```yaml theme={null}
streams:
  projects:
    with:
      user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
    query: SELECT projects.* FROM projects INNER JOIN user_orgs ON user_orgs.org_id = projects.org_id
```

## Combining with Multiple Queries

CTEs work well with the `queries` feature (multiple queries per stream). This lets you share the CTE and keep all query results in one stream: the client only needs to manage one subscription instead of multiple.

```yaml theme={null}
streams:
  user_data:
    with:
      my_org: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
    queries:
      - SELECT * FROM projects WHERE org_id IN my_org
      - SELECT * FROM repositories WHERE org_id IN my_org
      - SELECT * FROM team_members WHERE org_id IN my_org
```

## Complete Example

A full configuration using both global and stream-level CTEs. The `accessible_projects` logic is shared across streams via a global CTE; `user_orgs` is only needed by `org_and_projects`, so it stays stream-level:

```yaml theme={null}
config:
  edition: 3

# Global CTE - shared across all streams
with:
  accessible_projects: |
    SELECT id FROM projects
    WHERE org_id IN (SELECT org_id FROM org_memberships WHERE user_id = auth.user_id())
      OR id IN (SELECT project_id FROM project_shares WHERE shared_with = auth.user_id())

streams:
  # Organization-level data (auto-sync) - stream-level CTE + global CTE
  org_and_projects:
    auto_subscribe: true
    with:
      user_orgs: |
        SELECT org_id FROM org_memberships WHERE user_id = auth.user_id()
    queries:
      - SELECT * FROM organizations WHERE id IN user_orgs
      - SELECT * FROM projects WHERE id IN accessible_projects

  # Project details (on-demand) - uses only the global CTE
  project_details:
    queries:
      - |
        SELECT * FROM tasks
        WHERE project_id = subscription.parameter('project_id')
          AND project_id IN accessible_projects
      - |
        SELECT * FROM files
        WHERE project_id = subscription.parameter('project_id')
          AND project_id IN accessible_projects
```

## Limitations

The following rules apply to CTEs. For the full syntax reference, see [Supported SQL — CTE and WITH syntax](/sync/supported-sql#cte-and-with-syntax).

**CTE names must not shadow source table names.** This prevents hard-to-debug situations where a global CTE silently changes the behavior of a stream defined elsewhere in the config.

**Local CTEs take precedence over global CTEs.** If a stream defines a CTE with the same name as a global CTE, the stream-level definition is used within that stream.

**CTEs cannot reference other CTEs.** Each CTE must be self-contained:

```yaml theme={null}
# This won't work - cte2 cannot reference cte1
streams:
  my_stream:
    with:
      cte1: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
      cte2: SELECT id FROM projects WHERE org_id IN cte1  # Error!
```

If you need to chain filters, use nested subqueries in your stream query instead:

```yaml theme={null}
streams:
  tasks:
    with:
      user_orgs: SELECT org_id FROM org_members WHERE user_id = auth.user_id()
    query: |
      SELECT * FROM tasks
      WHERE project_id IN (
        SELECT id FROM projects WHERE org_id IN user_orgs
      )
```

**The short-hand `IN cte_name` works only when the CTE has exactly one column.** If the CTE has multiple columns, use explicit subquery syntax or join the CTE as a table.

**Stream-level CTE names take precedence over table/collection names.** If you define a stream-level CTE with the same name as a database table/collection, the CTE is used within that stream. Choose distinct names to avoid confusion.