# Pipe syntax

> BigQuery's pipe syntax writes a query as a top-to-bottom chain of steps joined by |>, in the order they run. Querylab.io converts standard SQL to pipe and back with one shortcut, and runs any prefix of the chain to show its rows.

Source: https://docs.querylab.io/writing-sql/pipe-syntax/

**Query 1 · pipe**

```sql
FROM `ace-analytics.warehouse.orders`
|> WHERE order_date >= '2024-01-01' AND status = 'completed'
|> AGGREGATE COUNT(*) AS orders, SUM(amount) AS revenue
   GROUP BY user_id
|> ORDER BY revenue DESC
|> LIMIT 20
```

The same query in standard SQL puts the `SELECT` first and the `FROM` after it, which is the reverse of what BigQuery does with it. Both forms run identically and cost the same.

**the same query, standard SQL**

```sql
SELECT user_id, COUNT(*) AS orders, SUM(amount) AS revenue
FROM `ace-analytics.warehouse.orders`
WHERE order_date >= '2024-01-01' AND status = 'completed'
GROUP BY user_id
ORDER BY revenue DESC
LIMIT 20
```

## Completions

After `|>` the completion list offers every pipe operator. Inside a step it lists the columns that step can see, including the ones an earlier `EXTEND`, `SELECT` or `AGGREGATE` created.

## Converting standard and pipe syntax

Standard to pipe and pipe to standard are both one action. They work on the whole document or, if you have a selection, on the selection alone.

| Action                   | Mac                                  | Windows / Linux                           |
| ------------------------ | ------------------------------------ | ----------------------------------------- |
| Convert to Pipe Syntax   | `⌥ ⌘ P` | `Ctrl Alt P` |
| Convert from Pipe Syntax | `⌥ ⌘ S` | `Ctrl Alt S` |

The same two commands are in the editor's right-click menu, and the ⋮ menu at the top right of the editor shows the one that applies to the query in the tab:

**editor ⋮ menu**

With pipe SQL in the tab the entry reads Convert to Standard and the Optimized variant is hidden.

**Convert to Pipe** is a faithful translation: every CTE and subquery stays where it was. **Convert to Pipe (Optimized)** rewrites the query the way you'd write it by hand: subqueries flattened, single-use CTEs inlined into the chain, and `SELECT` steps that only re-list existing columns dropped.

**before · standard**

```sql
WITH completed AS (
  SELECT user_id, amount
  FROM `ace-analytics.warehouse.orders`
  WHERE status = 'completed'
)
SELECT user_id, SUM(amount) AS revenue
FROM completed
GROUP BY user_id
HAVING SUM(amount) > 1000
```

**after · Convert to Pipe (Optimized)**

```sql
FROM `ace-analytics.warehouse.orders`
|> WHERE status = 'completed'
|> AGGREGATE SUM(amount) AS revenue
   GROUP BY user_id
|> WHERE revenue > 1000
```

`HAVING` has no pipe operator: it becomes a `WHERE` after `AGGREGATE`, which can use the aggregate's alias.

- The statement has a `FROM`. `SELECT 1 + 1` has nothing to pipe from and is     left unchanged.
  - For a large script, select one statement and convert just that.

The same converter runs without an account at [querylab.io/tools/pipe-to-sql](https://querylab.io/tools/pipe-to-sql).

## Running part of the chain

Every `|>` gets an icon in the gutter. Hover it for the SQL up to that step; click to dry-run that prefix, Shift-click to run it and see its rows in the result grid. That is the fastest way to find which step lost the rows you expected. See [partial execution](https://docs.querylab.io/writing-sql/partial-execution/).

**hover on the gutter icon of step 2**

```sql
FROM `ace-analytics.warehouse.orders`
|> WHERE status = 'completed'
|> «hl:AGGREGATE COUNT(*) AS orders GROUP BY user_id»
|> ORDER BY orders DESC
```

Click: Re-estimate • Shift+Click: Run

## Operators and standard equivalents

All BigQuery pipe operators are supported. Where standard SQL has an equivalent it's in the first column.

| Standard SQL                        | Pipe                                  |
| ----------------------------------- | ------------------------------------- |
| `SELECT cols FROM t`                | `FROM t \|> SELECT cols`              |
| `WHERE cond`                        | `\|> WHERE cond`                      |
| `GROUP BY cols` + aggregates        | `\|> AGGREGATE … GROUP BY cols`       |
| `HAVING cond`                       | `\|> WHERE cond` after `AGGREGATE`    |
| `SELECT DISTINCT *`                 | `\|> DISTINCT`                        |
| `JOIN t ON …`                       | `\|> JOIN t ON …`                     |
| `ORDER BY cols`                     | `\|> ORDER BY cols`                   |
| `LIMIT n [OFFSET m]`                | `\|> LIMIT n [OFFSET m]`              |
| `AS alias`                          | `\|> AS alias`                        |
| `WITH cte AS (…)`                   | `\|> WITH cte AS (…)`                 |
| `TABLESAMPLE SYSTEM (n PERCENT)`    | `\|> TABLESAMPLE SYSTEM (n PERCENT)`  |
| `PIVOT(agg FOR col IN (…))`         | `\|> PIVOT(agg FOR col IN (…))`       |
| `UNPIVOT(val FOR name IN (…))`      | `\|> UNPIVOT(val FOR name IN (…))`    |
| `FROM ML.PREDICT(MODEL m, TABLE t)` | `FROM t \|> CALL ML.PREDICT(MODEL m)` |
| `UNION ALL` / `UNION DISTINCT`      | `\|> UNION ALL (…)`                   |
| `INTERSECT DISTINCT`                | `\|> INTERSECT DISTINCT (…)`          |
| `EXCEPT DISTINCT`                   | `\|> EXCEPT DISTINCT (…)`             |
| `MATCH_RECOGNIZE(…)`                | `\|> MATCH_RECOGNIZE(…)`              |
| —                                   | `\|> EXTEND expr AS alias`            |
| —                                   | `\|> SET col = expr`                  |
| —                                   | `\|> DROP col1, col2`                 |
| —                                   | `\|> RENAME old AS new`               |

### Column operators without a standard form

`EXTEND`, `SET`, `DROP` and `RENAME` change the column list without restating it. Converting to standard SQL expands them into a `SELECT` with the full list.

```sql
FROM `ace-analytics.warehouse.users`
|> EXTEND UPPER(country) AS country_code
|> SET email = LOWER(email)
|> DROP password_hash, salt
|> RENAME created_at AS signup_at
```

For the operators themselves, see [BigQuery's pipe syntax reference](https://cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax).

## Mixing pipe and standard syntax

Pipe operators can follow a standard query, and a pipe query can be a CTE or a subquery in a standard one. The converter handles both directions of that, and the editor works the same inside either part.

```sql
WITH cleaned AS (
  FROM `ace-analytics.warehouse.events`
  |> WHERE event_date >= '2024-01-01'
  |> SELECT user_id, event_type
)
SELECT user_id, COUNT(*) AS events
FROM cleaned
GROUP BY user_id
|> WHERE events > 10
```

## Formatting a pipe query

The formatter keeps the chain one step per line. Settings → Editor → SQL Formatting → Pipe Operator Style chooses between **Standard** (pipes indented with the content) and **Left-Aligned** (`|>` in column 0, keywords indented). See [SQL formatting](https://docs.querylab.io/writing-sql/sql-formatting/).