Common Table Expressions (CTE) in SQL

A Common Table Expression (CTE) is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE can only be referenced by the statement that immediately follows it. A CTE allows access to functionality within that single SQL statement that was previously only available through use of functions, temp tables, cursors, and so on.

A CTE can also be thought of a disposable view. No indexes or additional statistics are stored for a CTE, and it functions as a shorthand for a sub-query.

A CTE is not stored as an object and lasts only for the duration of the query. So, it is similar to a derived table. However, unlike a derived table, a CTE can be self-referencing (recursive CTE) and referenced multiple times in the same query.

A CTE can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

When could a Common Table Expression be used?

A CTE could be used to fulfill the following requirements:
  • To create a recursive query.
  • When the general use of a view is not required, a CTE can be substituted for a view; that is, you do not have to store the definition in metadata.
  • A CTE can be used to enable grouping by a column that is derived from a scalar sub-select, or a function that is either not deterministic or has external access.
  • A CTE can be used to reference the resulting table multiple times in the same statement.

Structure of a CTE

WITH expression_name [ ( column_name [,...n] ) ]
( CTE_query_definition )
SELECT <column_list>
FROM expression_name;


;WITH WebsiteDetails AS -- notice the semicolon here
SELECT ROW_NUMBER() OVER (ORDER BY in_website_id) AS [No.],
in_website_id, vc_website_domain_name
WHERE bt_active = 1
SELECT * FROM WebsiteDetails

Note: The semicolon before WITH is used basically to terminate previous SQL statements to avoid errors.

No comments:

Post a Comment