Filter and select in a pipeline
Long queries get hard to read when every clause depends on the ones around it. Pipe syntax lets you read a query in the order it runs.
How it works
In pipe syntax, FROM creates the input table. Each |> step takes the rows from the step before it. WHERE filters them, and SELECT chooses the final columns. FROM customers is short for learn.shop.customers: the shop dataset is the default in these lessons.
- Start a pipeline with
FROMtable or a query in parentheses. - In these lessons
FROM customersis short forlearn.shop.customers. Theshopdataset is the default, so you can leavelearn.shop.off. |> WHERE conditionremoves rows and keeps every column.|> SELECT expressionsreturns only the columns and expressions you name.
Common mistake
A step can only use the columns that the steps before it kept. If an earlier step drops a column, a later step cannot use it.
Worked example
FROM customers
|> WHERE state = 'TX'
|> SELECT customer_name, state
|> LIMIT 3The whole customers table enters the pipeline, and only the Texas rows continue.
SELECT keeps two columns, and the last step limits the output to three rows.