Knowledge Science
Grasp the important thing variations between CTEs and subqueries to know precisely when and the place to make use of every!
Ought to I take advantage of a CTE or a Subquery in SQL?? — you should be asking this query very often.
I had it too!
A subquery, as its identify suggests, is a question inside one other question, whereas CTE or a Widespread Desk Expression is a short lived outcome set that you simply outline individually. Each subquery and CTE might be so simple as utilizing some filter and as advanced as doing a posh knowledge transformation.
And this similarity is commonly the supply of confusion about why and when to make use of both of them.
Once I began with knowledge science, I all the time struggled to make this selection. Nonetheless, as years handed, I noticed, it’s much less about “higher”, and extra about the precise match to get the job carried out.
For instance, after I work on a undertaking with advanced logic, the place I would like to interrupt down knowledge transformations or work with hierarchical knowledge — I take advantage of CTEs. However I take advantage of subqueries after I have to do one-off calculations or filtering.
For an in depth dialogue on CTE, I extremely advocate studying —