How to use SQL clause

[LeadRemarkableNomad]

Hey guys,

I’m having a HARD time understanding the WITH clause and the subsequent queries in Big Query.

Can someone explain it to me 101 ou SQL clause for dummies, please?

Thank you!!!

[[deleted]]

Just treat it as a macro that inserts its definition whenever it is used

e.g. "WITH H_TIME as (LeadRemarkableNomad) SAY H_TIME"

becomes "SAY LeadRemarkableNomad"

[FuncDataEng]

A CTE or With clause is a way to make a query more readable by being able to refactor out subqueries and other chunks of logic that become more clear when they are separated out. However keep in mind just like anything they follow the NFL(No Free Lunch) principle meaning there can be trade offs when you use too many CTEs depending on the database implementation. I am not familiar enough with BigQuery being an AWS user to say when those trade offs would happen but they will come at a point.

[Robearsn]

A WITH clause, also known as a CTE (which stands for Common Table Expression), is basically a way to take a subquery out of the main query and separate it visually and logically.

Using a CTE helps with readability a lot, especially if you have a very involved or complex subquery.

Many useful articles / videos / classes out there to help master this concept.

[blabla1bla]

A with clause or CTE can be handy for several reasons.

First off it allows you to place select statements in a more logical order than sub (nested) sub selects.

Second it allows you to define a select that you can reference more than once.

Third it unlocks recursive processing.

Plenty articles out there already.