SQL pipe syntax
Write SQL top to bottom with the |> operator, in the order the query actually runs.
On this page
Show code in
Every code block on the page follows this.
You will learn
- What SQL pipe syntax is
- How each pipe operator maps to classic SQL
- Why top-to-bottom order helps readability and debugging
- How to mix it with existing queries
FROM table and chain operations with |>, in the order they execute. It compiles to the same plan as classic SQL.Why a new syntax
Classic SQL is written in a different order from how it runs: SELECT comes first but is evaluated nearly last, and filtering an aggregate needs HAVING or a subquery. Pipe syntax lets you write the steps in execution order, the way you would chain DataFrame methods.
Side by side
FROM orders |> WHERE status = 'PAID' |> AGGREGATE SUM(amount) AS revenue GROUP BY customer_id |> WHERE revenue > 1000 |> ORDER BY revenue DESC |> LIMIT 10
SELECT customer_id, SUM(amount) AS revenue FROM orders WHERE status = 'PAID' GROUP BY customer_id HAVING SUM(amount) > 1000 ORDER BY revenue DESC LIMIT 10
(orders.filter("status = 'PAID'") .groupBy("customer_id").agg(F.sum("amount").alias("revenue")) .filter("revenue > 1000") .orderBy(F.desc("revenue")) .limit(10))
Note how the pipe version reads exactly like the DataFrame chain. The second WHERE replaces HAVING: it simply filters the output of the previous step.
Operators
| Pipe operator | Does | Classic equivalent |
|---|---|---|
|> WHERE cond | Filter the current rows | WHERE, HAVING or QUALIFY, depending on position |
|> SELECT exprs | Replace the columns | SELECT list |
|> EXTEND expr AS name | Add columns, keep the rest | SELECT *, expr AS name |
|> SET col = expr | Replace a column in place | withColumn |
|> DROP col | Remove columns | SELECT listing all others |
|> AGGREGATE aggs GROUP BY keys | Aggregate | GROUP BY |
|> [LEFT] JOIN t ON ... | Join another table | JOIN |
|> ORDER BY, |> LIMIT | Sort, limit | ORDER BY, LIMIT |
|> AS alias | Name the intermediate result | Subquery alias |
|> UNION ALL, PIVOT, UNPIVOT, TABLESAMPLE | Set operations and reshaping | The same clauses |
Why engineers like it
- Debugging: delete the lines after any step and the query still runs, showing the intermediate result.
- No nesting: a filter after an aggregation or a window needs no subquery.
- Incremental: each line can only refer to columns that exist at that point, which makes queries easier to review.
- Compatible: pipe and classic SQL can be mixed; a classic query can be followed by
|>operators.
Common mistakes
Expecting it on Spark 3.x
Using SELECT to add a column
Assuming it is faster
Key takeaways
- Pipe syntax writes SQL in execution order: FROM, then |> steps.
- A WHERE after AGGREGATE replaces HAVING.
- EXTEND adds columns; SELECT replaces them.
- It compiles to the same plan: readability, not speed.
Check yourself
3 questions1. How do you add a column while keeping the others in pipe syntax?
Show the answer
|> EXTEND. EXTEND appends computed columns.
2. What replaces HAVING in pipe syntax?
Show the answer
A |> WHERE after |> AGGREGATE. WHERE filters whatever the previous step produced, including aggregates.
3. Is a pipe query faster than the classic version?
Show the answer
No, it produces the same plan. It is parsed into the same logical plan.
Go deeper
Catalyst and physical plansPySpark functions
groupBy + aggSpark internals
ANSI mode by default
Primary sources: Spark SQL reference