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

# SELECT

> Reference for the SELECT statement and all its clauses

The `SELECT` statement retrieves data from one or more datasets.

## Syntax

```sql theme={null}
SELECT [DISTINCT]
  column1 [AS alias1],
  column2 [AS alias2],
  ...
FROM table_reference [AS alias]
[JOIN table_reference ON condition]
[WHERE condition]
[GROUP BY column1, column2, ...]
[HAVING condition]
[QUALIFY condition]
[ORDER BY column1 [ASC|DESC], ...]
[LIMIT n [OFFSET m] ROWS]
```

<Note>
  NQL requires explicit column lists. Wildcards (`SELECT *` and `COUNT(*)`) are not supported. See [Explicit Column Selection](/nql/general/explicit-columns) for details.
</Note>

## Clause order

Clauses must appear in this order:

1. `SELECT` — columns to return
2. `FROM` — data source(s)
3. `JOIN` — additional data sources with join conditions
4. `WHERE` — row-level filters applied before aggregation
5. `GROUP BY` — grouping for aggregations
6. `HAVING` — filters applied after aggregation
7. `QUALIFY` — filters based on window function results
8. `ORDER BY` — result ordering
9. `LIMIT` — result count restriction

## Examples

**Basic query:**

```sql theme={null}
SELECT
  user_id,
  email,
  created_at
FROM company_data."123"
WHERE created_at > '2024-01-01'
```

**Query with aggregation:**

```sql theme={null}
SELECT
  region,
  COUNT(1) AS user_count,
  AVG(lifetime_value) AS avg_ltv
FROM company_data."456"
GROUP BY region
HAVING COUNT(1) > 100
ORDER BY avg_ltv DESC
```

**Query with window function and QUALIFY:**

```sql theme={null}
SELECT
  user_id,
  email,
  last_seen
FROM company_data."123"
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY last_seen DESC
) = 1
```

***

## Table references

### Dataset references

Reference your datasets through the `company_data` schema, using either the dataset's numeric ID or its name:

```sql theme={null}
FROM company_data."123"
FROM company_data.my_dataset
```

Numeric IDs must be quoted because they are numeric. Dataset names don't need quoting unless the name is a reserved keyword or contains special characters, in which case wrap it in double quotes.

### Table aliases

Assign aliases to simplify column references:

```sql theme={null}
FROM company_data."123" AS users
SELECT users.email, users.created_at
```

### Rosetta Stone table references

Rosetta Stone provides normalized access to data through three scope levels: global, company-scoped, and dataset-scoped. Each scope determines which data sources your query accesses.

<Note>
  To access another company's data through Rosetta Stone, that company must have
  shared their data with you via an access rule. Your own company's data
  (`company_data`) is always accessible. See [Access Rules](/concepts/primitives/access-rules)
  for details on data sharing.
</Note>

#### Global access

Query all normalized data from all datasets shared with you:

```sql theme={null}
SELECT
  narrative.rosetta_stone.unique_id,
  narrative.rosetta_stone.event_timestamp
FROM narrative.rosetta_stone
WHERE narrative.rosetta_stone.event_timestamp >= CURRENT_DATE - INTERVAL '30' DAY
```

#### Company-scoped access

Query normalized data from a specific company's datasets:

```sql theme={null}
-- All datasets from your company
SELECT
  company_data._rosetta_stone.unique_id,
  company_data._rosetta_stone.event_timestamp
FROM company_data._rosetta_stone

-- All datasets from a specific company (by slug)
SELECT
  partner_company._rosetta_stone.unique_id,
  partner_company._rosetta_stone.event_timestamp
FROM partner_company._rosetta_stone
```

#### Dataset-scoped access

Query normalized data from a specific dataset:

```sql theme={null}
-- By dataset ID
SELECT
  company_data."123"._rosetta_stone.unique_id,
  company_data."123"._rosetta_stone.event_timestamp
FROM company_data."123"._rosetta_stone

-- By dataset slug
SELECT
  company_data.my_dataset._rosetta_stone.unique_id,
  company_data.my_dataset._rosetta_stone.event_timestamp
FROM company_data.my_dataset._rosetta_stone

-- From another company's dataset
SELECT
  partner_company.shared_dataset._rosetta_stone.unique_id,
  partner_company.shared_dataset._rosetta_stone.event_timestamp
FROM partner_company.shared_dataset._rosetta_stone
```

#### Choosing a scope level

| Scope   | Syntax                              | Use when                                                                        |
| ------- | ----------------------------------- | ------------------------------------------------------------------------------- |
| Global  | `narrative.rosetta_stone`           | You want all available normalized data from all sources                         |
| Company | `company_data._rosetta_stone`       | You want data from a specific company only                                      |
| Dataset | `company_data."123"._rosetta_stone` | You need to combine normalized and non-normalized columns from the same dataset |

See [Scoping Your Queries](/concepts/rosetta-stone/normalization-model#scoping-your-queries) for guidance on choosing the right scope.

### Access rules

Query through pre-configured access rules using the provider's company slug:

```sql theme={null}
FROM provider_company."access_rule_name"

SELECT
  provider_company."ar_fitness".Total_Output
FROM provider_company."ar_fitness"
```

***

## Column references

### Fully qualified names

Use fully qualified column names to avoid ambiguity, especially in joins:

```sql theme={null}
SELECT
  company_data."123".user_id,
  company_data."123".email,
  company_data."456".order_id
FROM company_data."123"
JOIN company_data."456" ON company_data."123".user_id = company_data."456".customer_id
```

### Column aliases

Assign aliases to rename columns in results:

```sql theme={null}
SELECT
  user_id AS id,
  email AS contact_email,
  UPPER(name) AS display_name
FROM company_data."123"
```

### Accessing nested fields

Access struct fields using dot notation:

```sql theme={null}
SELECT
  company_data."1".nested_field.deep_field.value
FROM company_data."1"
```

Access array elements using bracket notation:

```sql theme={null}
SELECT
  identifiers[0] AS first_id,
  identifiers[0].type AS first_id_type
FROM company_data."1"
```

<Tip>
  Bracket notation also works for struct fields: `data['field_name']`. This avoids needing to quote reserved keywords. See [Reserved Keywords best practices](/nql/general/reserved-keywords#prefer-bracket-notation-for-property-access).
</Tip>

***

## Special columns

### Price column

Every dataset includes `_price_cpm_usd` (price per 1,000 rows). Use it to filter by cost:

```sql theme={null}
WHERE company_data."123"._price_cpm_usd <= 1.00
```

### Rosetta Stone field access

Access normalized identity data through the `._rosetta_stone` special field on any dataset:

```sql theme={null}
SELECT
  company_data."123"._rosetta_stone.unique_id,
  company_data."123".original_column
FROM company_data."123"
```

This allows you to access both normalized Rosetta Stone attributes and non-normalized columns from the same dataset row.

#### Access specific identifier types

Query specific identifier types using the `unique_id` struct:

```sql theme={null}
SELECT
  narrative.rosetta_stone.unique_id.b."type",
  narrative.rosetta_stone.unique_id.b."value"
FROM narrative.rosetta_stone
WHERE narrative.rosetta_stone.unique_id.b."type" NOT IN ('tdid', 'cookie')
```

For complete details on scope levels and table references, see [Rosetta Stone table references](#rosetta-stone-table-references).

***

## DELTA tables

The `DELTA` function returns only records that have changed since the last query execution. Use it for incremental data processing.

### Syntax

```sql theme={null}
FROM DELTA(TABLE table_reference)
FROM DELTA((subquery))
```

### Examples

**Delta on a dataset:**

```sql theme={null}
SELECT column1
FROM DELTA(TABLE company_data."1") AS t
```

**Delta on Rosetta Stone:**

```sql theme={null}
SELECT long_value
FROM DELTA(TABLE narrative.rosetta_stone)
```

**Delta with filtering:**

```sql theme={null}
SELECT long_value
FROM DELTA((
  SELECT long_value
  FROM company_data.rosetta_stone
  WHERE long_value > 42
))
```

***

## Subqueries

### Subquery in WHERE

```sql theme={null}
SELECT column1
FROM company_data."1"
WHERE value > (
  SELECT AVG(value)
  FROM company_data."1"
)
```

### Subquery in FROM

```sql theme={null}
SELECT sub.total
FROM (
  SELECT SUM(amount) AS total
  FROM company_data."1"
  GROUP BY category
) AS sub
WHERE sub.total > 1000
```

### EXISTS and NOT EXISTS

```sql theme={null}
SELECT t1.column1
FROM company_data."1" t1
WHERE NOT EXISTS (
  SELECT 1
  FROM company_data."2" t2
  WHERE t1.id = t2.id
)
```

***

## Common Table Expressions (WITH)

CTEs let you define named subqueries for reuse within a statement.

### Syntax

```sql theme={null}
WITH cte_name AS (
  SELECT ...
),
another_cte AS (
  SELECT ...
)
SELECT ...
FROM cte_name
```

### Example

```sql theme={null}
CREATE MATERIALIZED VIEW "high_value_users"
AS (
  WITH daily_totals AS (
    SELECT
      user_id,
      DATE_TRUNC('day', event_timestamp) AS event_date,
      SUM(amount) AS daily_spend
    FROM company_data."1"
    GROUP BY user_id, DATE_TRUNC('day', event_timestamp)
  ),
  user_averages AS (
    SELECT
      user_id,
      AVG(daily_spend) AS avg_daily_spend
    FROM daily_totals
    GROUP BY user_id
  )
  SELECT
    user_id,
    avg_daily_spend
  FROM user_averages
  WHERE avg_daily_spend > 100
)
BUDGET 25 USD
```

***

## QUALIFY clause

The `QUALIFY` clause filters results based on window function values. It's evaluated after window functions, making it useful for deduplication and ranking.

### Syntax

```sql theme={null}
SELECT columns
FROM table
QUALIFY window_function_condition
```

### Deduplication example

Keep only the most recent record per user:

```sql theme={null}
SELECT
  user_id,
  email,
  last_seen
FROM company_data."123"
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY last_seen DESC
) = 1
```

### Multiple window conditions

```sql theme={null}
SELECT column1
FROM company_data."1"
QUALIFY
  ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col) = 1
  AND SUM(1) OVER (PARTITION BY other_col) < 10
```

***

## Budget clauses

Budget clauses control spending on data queries. They are required for `CREATE MATERIALIZED VIEW` statements.

### BUDGET

Set a maximum total spend:

```sql theme={null}
BUDGET 50 USD
```

### LIMIT with USD

Alternative syntax for budget limits:

```sql theme={null}
LIMIT 100 USD
```

### Recurring limits

Set budget limits per time period:

```sql theme={null}
LIMIT 500 USD PER CALENDAR_MONTH
LIMIT 50 USD PER CALENDAR_DAY
```

### Row limits

Limit by row count instead of cost:

```sql theme={null}
LIMIT 1000 ROWS
LIMIT 100 OFFSET 10 ROWS
```

***

## SELECT DISTINCT

Remove duplicate rows from results:

```sql theme={null}
SELECT DISTINCT category
FROM company_data."123"
```

***

## CASE expressions

Conditional logic within queries:

```sql theme={null}
SELECT
  user_id,
  CASE
    WHEN lifetime_value > 1000 THEN 'high'
    WHEN lifetime_value > 100 THEN 'medium'
    ELSE 'low'
  END AS value_tier
FROM company_data."123"
```

***

## Related content

<CardGroup cols={2}>
  <Card title="Data Types" icon="shapes" href="/nql/data-types">
    Primitive and complex types supported in NQL
  </Card>

  <Card title="Operators" icon="calculator" href="/nql/operators">
    Comparison, logical, and arithmetic operators
  </Card>

  <Card title="Functions" icon="function" href="/nql/functions/index">
    Built-in and Narrative-specific functions
  </Card>

  <Card title="CREATE MATERIALIZED VIEW" icon="list-check" href="/nql/commands/create-materialized-view">
    Complete CREATE MATERIALIZED VIEW reference
  </Card>
</CardGroup>