Almost all SELECT statements that join tables and use the join operator can be rewritten as subqueries, and vice versa. Writing the SELECT statement using the join operator is often easier to read and understand and can also help the Database Engine to find a more efficient strategy for retrieving the appropriate data. However, there […]Read More...
Table expressions are subqueries that are used where a table is expected. There are two types of table expressions: Derived tables Common table expressions A derived table is a table expression that appears in the FROM clause of a query. You can apply derived tables when the use of column aliases is not possible because […]Read More...
A common table expression (CTE) is a named table expression that is supported by Transact-SQL. There are two types of queries that use CTE: Nonrecursive queries Recursive queries The following sections describe both query types. Note – Common table expressions are also used by the APPLY operator, which allows you to invoke a table-valued function […]Read More...
You can use CTEs to implement recursion because CTEs can contain references to themselves. The basic syntax for a CTE for recursive queries is
WITH cte_name (column_list) AS
cte_name and column_list have the same meaning as in CTEs for nonrecursive queries. The body of the WITH clause comprises two queries that are connected with the UNION ALL operator. […]Read More...