Advanced query techniques
Structure queries with CTEs
Readable names with one-statement scope.
Understand the concept
A common table expression names a query using WITH. Its scope is the following single statement; it is neither a permanent table nor an automatic cache. Separate multiple CTE definitions with commas. Terminating the previous statement with a semicolon avoids ambiguity.
A CTE makes stages such as order totals followed by customer metrics readable. Multiple references do not guarantee a single physical computation. Compare with a temporary table when reuse or indexing matters.
Use it to divide long queries into meaningful transformation stages.
Treating a CTE as a permanent table, materialised result or automatic optimisation.
One topic. Three levels of depth.
WITH InStock AS (SELECT ProductId, ProductName FROM lab.Product WHERE Stock > 0) SELECT * FROM InStock;Names the in-stock product set within one statement.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Build category counts in a CTE, then filter counts of at least two.
Show the solution and explanation
WITH Counts AS (SELECT Category, COUNT(*) AS Products FROM lab.Product GROUP BY Category) SELECT * FROM Counts WHERE Products >= 2;Electronics contains five products, not six; the dataset has seven products in total.