WITH common_table_expression

Question

Dear All,

I'm using SQL server 2016. On the page Microsoft Documentation, I see the following code. However, I don't understand the meaning of the paragraph under the heading CTE_query_definition. Could anybody tell me the meaning of this paragraph?

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
    expression_name [ ( column_name [ ,...n ] ) ]
    AS
    ( CTE_query_definition )

Answer 1

Hi,

Below the syntax, there is an explanation of the parameters that appear in the syntax.

expression_name A valid identifier for the common table expression. expression_name must be different from the name of any other common table expression defined in the same WITH <common_table_expression> clause, but expression_name can be the same as the name of a base table or view. Any reference to expression_name in the query uses the common table expression and not the base object.

column_name Specifies a column name in the common table expression. Duplicate names within a single CTE definition aren't allowed. The number of column names specified must match the number of columns in the result set of the CTE_query_definition. The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

CTE_query_definition Specifies a SELECT statement whose result set populates the common table expression. The SELECT statement for CTE_query_definition must meet the same requirements as for creating a view, except a CTE can't define another CTE. For more information, see the Remarks section and CREATE VIEW (Transact-SQL). If more than one CTE_query_definition is defined, the query definitions must be joined by one of these set operators: UNION ALL, UNION, EXCEPT, or INTERSECT.

For some of the symbols that appear in the syntax, you can refer to Transact-SQL's syntax conventions.

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/transact-sql-syntax-conventions-transact-sql?view=sql-server-ver16

In addition, I would like to say that if you just look at the syntax, it is really difficult to grasp quickly. You might as well take a look at the example below, start with a simple example, and try to create a simple CTE yourself. Then gradually contact multi-CTE, recursion and other in-depth content.

My advice is to look at more examples and practice more. You will gradually master CTE. Good luck!

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

That's the in programming world common Backus–Naur form to describe a language, see

https://en.wikipedia.org/wiki/Backus%E2%80%93Naur_form