# Reusable column aliases in BigQuery

> With reusable column aliases on, a SELECT alias can be used by later SELECT items and the WHERE of the same query. How to turn the option on per project and region, where an alias is visible, which name wins on a clash, and what BigQuery refuses.

Source: https://docs.querylab.io/bigquery/column-aliases/

Updated: 2026-10-17

A reusable column alias is a name you give an expression in the `SELECT` list and then use again, further right in the same list or in the `WHERE` of the same query. `SELECT net_price * 1.25 AS gross_price, gross_price * 0.5 AS discounted` works without a subquery or a CTE. It only works when the project option `reusable_column_aliases_mode` is on, and it is off by default. With the option off, the same query fails with `Unrecognized name`.

## It is off until someone turns it on

**reading an alias with the option off**

```sql
SELECT net_price, net_price * 1.25 AS gross_price, gross_price * 0.5 AS discounted
FROM UNNEST([8.0, 20.0, 40.0]) AS net_price
```

BigQuery rejects this query:

```text
Unrecognized name: gross_price at [1:52]
```

BigQuery looks for `gross_price` among the columns of the `FROM` clause, finds none, and stops. The usual fix is a subquery or a CTE that defines `gross_price` one level down.

With the option on, the same query runs. Each alias is visible to the items after it, left to right:

**the same query with the option on**

```sql
SELECT net_price, net_price * 1.25 AS gross_price, gross_price * 0.5 AS discounted
FROM UNNEST([8.0, 20.0, 40.0]) AS net_price
```

Result in BigQuery (3 rows):

| net_price | gross_price | discounted |
| --- | --- | --- |
| 8.0 | 10.0 | 5.0 |
| 20.0 | 25.0 | 12.5 |
| 40.0 | 50.0 | 25.0 |

`gross_price` is 10 for the first row, and `discounted` is half of it. The alias can also drive the `WHERE`:

**filtering on an alias**

```sql
SELECT product, net_price * 1.25 AS gross_price
FROM UNNEST([STRUCT('pen' AS product, 4.0 AS net_price), ('lamp', 20.0), ('desk', 160.0)])
WHERE gross_price BETWEEN 5 AND 100
ORDER BY gross_price
```

Result in BigQuery (2 rows):

| product | gross_price |
| --- | --- |
| pen | 5.0 |
| lamp | 25.0 |

## Turn it on

The option is a project setting. You need the BigQuery Admin role, or the `bigquery.projects.update` permission on the project. Set it with `ALTER PROJECT` and a region qualifier:

```sql
ALTER PROJECT `my-project`
SET OPTIONS (`region-us.reusable_column_aliases_mode` = "on");
```

The qualifier and the option name go in one pair of backticks. Without the region, BigQuery refuses the statement and says the option "is a regional project option and must be backtick-enclosed and preceded by a region qualifier".

A few facts that decide whether it works for you:

- **It is per project and per region.** Setting it for `region-us` does nothing for a job that runs in `EU` or `us-central1`, and does nothing for another project.
- **It takes about two minutes to apply.** For a while after the statement succeeds, some jobs still behave as if the option were off. Retry before you trust a result.
- **The value is a string.** `"on"`, `"On"` and `'on'` are the same. `TRUE` fails with a type error and an unknown word such as `"bogus"` fails with `'bogus' is not a valid value`.
- **`"off"` and `NULL` both turn it off.** Setting it to `NULL` restores the default.
- **An organization can set it too.** `ALTER ORGANIZATION SET OPTIONS (`region-us.reusable_column_aliases_mode` = "on")` takes the same value and needs the `bigquery.organizations.update` permission. We checked that the statement validates with a dry run but did not run it.

### Check whether it is on

The cheapest check is a dry run of a query that reads an alias. It processes 0 bytes and needs no access to `INFORMATION_SCHEMA`:

**the check query with the option off**

```sql
SELECT 1 AS a, a + 1 AS b
```

BigQuery rejects this query:

```text
Unrecognized name: a at [1:16]
```

If the query validates, the option is on for that project and region. `Unrecognized name: a` means it is off. Run the dry run in the project and location your queries use, because the answer is per region.

You can also read the setting. `INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS` has a row for the option while it is set, with the value `ON`. No row means the default, which is off. `PROJECT_OPTIONS` lists it as well, and it can take about 20 seconds to show a change. Both need permission to read the views, and the effective view scans enough data to be billed the 10 MB minimum. See [INFORMATION_SCHEMA](https://docs.querylab.io/bigquery/information-schema/).

## Where an alias is visible

With the option on, an alias can be read:

- by later items in the same `SELECT` list, never by earlier ones;
- in the `WHERE`, `GROUP BY`, `HAVING`, `ORDER BY`, `QUALIFY` and `WINDOW` clauses of the same query block, and inside `OVER (...)`;
- by scalar, `EXISTS` and `IN` subqueries in the select list or the `WHERE`;
- after `*`, and under `DISTINCT` and `SELECT AS STRUCT`;
- through a struct path: after `STRUCT(1 AS a) AS s`, the expression `s.a + 1` works.

Names are matched without regard to case, backticked names included. An alias of an aggregate or a window function can be used inside a plain expression in a later item.

A subquery sees the alias too:

**a subquery reads the alias**

```sql
SELECT amount * 2 AS doubled, (SELECT doubled * 10) AS scaled
FROM UNNEST([1, 2]) AS amount
```

Result in BigQuery (2 rows):

| doubled | scaled |
| --- | --- |
| 2 | 20 |
| 4 | 40 |

Order matters. An item cannot read an alias that is defined after it:

**reading an alias before it is defined**

```sql
SELECT gross_price * 0.5 AS discounted, net_price * 1.25 AS gross_price
FROM UNNEST([8.0, 20.0]) AS net_price
```

BigQuery rejects this query:

```text
Unrecognized name: gross_price at [1:8]
```

An alias is computed once per row. `SELECT RAND() AS r, r = r` returns true, because both reads see the same value.

### Where it is not visible

`JOIN ... ON` cannot read an alias. The `ON` condition belongs to the `FROM` clause, not to the select list:

**an alias in a join condition**

```sql
WITH orders AS (
  SELECT 1 AS id, 10 AS customer_id UNION ALL SELECT 2, 20
),
customers AS (
  SELECT 11 AS id, 'Ada' AS name UNION ALL SELECT 21, 'Grace'
)
SELECT o.id, o.customer_id + 1 AS customer_key, c.name
FROM orders AS o
JOIN customers AS c ON c.id = customer_key
```

BigQuery rejects this query:

```text
Unrecognized name: customer_key; Did you mean customer_id? at [9:31]
```

Branches of a `UNION` do not share aliases either. [Pipe syntax](https://docs.querylab.io/bigquery/pipe-syntax/) has no reusable aliases inside one operator. The outputs of an operator are visible to the operators after it, but not to its own expressions:

**two aliases in one EXTEND**

```sql
FROM UNNEST([1, 2, 3]) AS x
|> EXTEND x * 2 AS doubled, doubled + 1 AS plus_one
```

BigQuery rejects this query:

```text
Unrecognized name: doubled at [2:29]
```

Put the second alias in a second `|> EXTEND` and it works. Pipe `|> SELECT` cannot use lateral aliases, and the documentation lists Graph Query Language as unsupported too.

## When the name is also something else

A name in a query can be a column, an alias, a column of an outer query, an argument of a function, or a script variable. BigQuery picks in this order:

1. a column of the `FROM` clause;
2. an alias of the select list;
3. a column of the outer query;
4. an argument of the user-defined function the query runs in;
5. a script variable.

A table column wins over an alias. Here `price` is both, and `price + 1` uses the column:

**a FROM column beats an alias**

```sql
SELECT 100 AS price, price + 1 AS plus_one
FROM UNNEST([5, 7]) AS price
```

Result in BigQuery (2 rows):

| price | plus_one |
| --- | --- |
| 100 | 6 |
| 100 | 8 |

The output column `price` is 100, but `plus_one` is 6 and 8, built from the column values 5 and 7.

An alias beats the outer-query column, the function argument and the script variable. This is the part to watch when you turn the option on, because a query that worked yesterday can change its answer. The query does not fail, so nothing tells you. The documentation calls this semantic drift.

An outer column is shadowed by an alias in a subquery. The query below compares `id` with `category_id`, and the subquery also defines an alias called `category_id`. We run it with the option off first:

**the subquery sees the outer category_id**

```sql
WITH product AS (
  SELECT 'pen' AS name, 1 AS category_id UNION ALL SELECT 'lamp', 2
)
SELECT name, category_id,
  (SELECT AS STRUCT id AS matched_id, 100 AS category_id
   FROM UNNEST([1, 2, 100]) AS id
   WHERE id = category_id) AS hit
FROM product
```

Result in BigQuery (2 rows):

| name | category_id | hit |
| --- | --- | --- |
| pen | 1 | {matched_id: 1, category_id: 100} |
| lamp | 2 | {matched_id: 2, category_id: 100} |

The `WHERE` compares with the outer row's `category_id`, 1 for the pen and 2 for the lamp, so each row matches its own id. With the option on, the subquery's `100 AS category_id` takes over:

**the alias category_id replaces the outer column**

```sql
WITH product AS (
  SELECT 'pen' AS name, 1 AS category_id UNION ALL SELECT 'lamp', 2
)
SELECT name, category_id,
  (SELECT AS STRUCT id AS matched_id, 100 AS category_id
   FROM UNNEST([1, 2, 100]) AS id
   WHERE id = category_id) AS hit
FROM product
```

Result in BigQuery (2 rows):

| name | category_id | hit |
| --- | --- | --- |
| pen | 1 | {matched_id: 100, category_id: 100} |
| lamp | 2 | {matched_id: 100, category_id: 100} |

Now both rows get `matched_id` 100, because the `WHERE` reads the alias.

A script variable is shadowed the same way. With the option off, `price_factor * 2` uses the variable:

**price_factor is the variable**

```sql
DECLARE price_factor INT64 DEFAULT 10;
SELECT net_price * 3 AS price_factor, price_factor * 2 AS doubled
FROM UNNEST([2, 4]) AS net_price
```

Result in BigQuery (2 rows):

| price_factor | doubled |
| --- | --- |
| 6 | 20 |
| 12 | 20 |

With the option on, it uses the alias:

**price_factor is the alias**

```sql
DECLARE price_factor INT64 DEFAULT 10;
SELECT net_price * 3 AS price_factor, price_factor * 2 AS doubled
FROM UNNEST([2, 4]) AS net_price
```

Result in BigQuery (2 rows):

| price_factor | doubled |
| --- | --- |
| 6 | 12 |
| 12 | 24 |

A function argument follows the same rule. In the documentation's example, a function with an argument `val` runs `SELECT AS STRUCT 5 AS val, val + 10`. With the option off it returns 110, from the argument 100. With the option on it returns 15, from the alias. Subqueries with their own `FROM` still win over the outer alias: `(SELECT y FROM UNNEST([7]) y)` reads 7.

The safe habit is to give an alias a name that no column, argument or variable in scope uses.

## What BigQuery refuses

With the option on, BigQuery refuses a few reads that would be ambiguous or have no defined value. The errors below are BigQuery's own.

### The same name twice

If two items define the same alias and a later item reads it, the read is ambiguous: `Column name y is ambiguous`. A duplicate that nobody reads is fine, and the result columns are named `y` and `y_1`. Only duplicates defined before the read count, so `SELECT 1 AS y, y + 1 AS z, 2 AS y` is accepted. In the `WHERE`, all duplicates count.

The name that comes from a column path counts as an alias for this rule. `s.price` is called `price`:

**s.price and 2 AS price**

```sql
SELECT s.price, 2 AS price, price + 1 AS next_price
FROM (SELECT STRUCT(1 AS price) AS s)
```

BigQuery rejects this query:

```text
Column name price is ambiguous at [1:29]
```

### Grouped queries

When the query groups or aggregates, an alias of a grouping expression cannot be read in the `WHERE` or inside the argument of an aggregate. Here `next_day` is an alias of the grouping column plus one:

**an alias in WHERE of a grouped query**

```sql
SELECT order_day + 1 AS next_day, COUNT(*) AS orders
FROM UNNEST([1, 1, 2, 3]) AS order_day
WHERE next_day > 2
GROUP BY order_day
```

BigQuery rejects this query:

```text
Column next_day is referenced laterally from WHERE or argument to an aggregation when there's grouping or aggregation in the query at [3:7]
```

The same message appears for an alias of an aggregate (`SUM(x) AS s ... WHERE s > 0`, or `SUM(s)`). It also fires when the read is nested: inside a subquery in the `WHERE`, inside a subquery in an aggregate argument, and with `GROUP BY ALL`. An aggregate that sits inside a nested subquery does not trigger it.

`HAVING` and `ORDER BY` run after the groups, so they accept the alias:

**the same condition in HAVING**

```sql
SELECT order_day + 1 AS next_day, COUNT(*) AS orders
FROM UNNEST([1, 1, 2, 3]) AS order_day
GROUP BY order_day
HAVING next_day > 2
ORDER BY next_day
```

Result in BigQuery (2 rows):

| next_day | orders |
| --- | --- |
| 3 | 1 |
| 4 | 1 |

### Window function aliases

An alias of a window function cannot be read in the `WHERE`, in `PARTITION BY` or in a window `ORDER BY`. A window function cannot take another window function's alias as an argument either:

**a window alias in WHERE**

```sql
SELECT sensor, COUNT(*) OVER () AS total
FROM UNNEST(['a', 'b', 'c']) AS sensor
WHERE total > 2
```

BigQuery rejects this query:

```text
Column total contains an analytic function, which is not allowed in WHERE clause at [3:7]
```

`SELECT COUNT(*) OVER () AS c, SUM(c) OVER ()` fails with `Analytic function cannot be an argument of another analytic function`. A window over a plain alias is fine, and so is `ROW_NUMBER() OVER (ORDER BY y)` for an alias `y`. With the option off, all of these are `Unrecognized name`. See [window functions](https://docs.querylab.io/bigquery/window-functions/).

### Subqueries without a FROM clause

A subquery with no `FROM` cannot read the alias of an aggregate or a window function:

**an aggregate alias in (SELECT total)**

```sql
SELECT SUM(amount) AS total, (SELECT total) AS again
FROM UNNEST([10, 20]) AS amount
```

BigQuery rejects this query:

```text
Column total contains an aggregate function, which is not allowed in SELECT without FROM clause at [1:38]
```

Give the subquery a `FROM`, even one over `UNNEST([1])`, and it reads the computed value.

## In Querylab.io

The editor checks the option for you. It dry-runs the check query `SELECT 1 AS a, a + 1 AS b` in the project and location of the active query tab, for free, and follows the answer. With the option on, an alias read is no longer flagged, completions offer the aliases defined so far, hover shows them, and the refusals above show up as errors with BigQuery's wording. With the option off, the read is flagged as an unrecognized name. If the check cannot tell, for example because the dry run failed, the editor treats the option as off and tries again after five minutes. The answer is kept per project and location for the session. When you switch to a tab in another project or region, the editor checks that one and follows it. See [Code quality](https://docs.querylab.io/writing-sql/code-quality/).

Try a SELECT alias reused later in the same query. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Work with reusable column aliases](https://docs.cloud.google.com/bigquery/docs/column-aliases)