SQL Talk

 Difference b/w CTE and Derived table:

 Difference between subquery and derived table:

Difference #1: CTEs can be recursive
Difference #2: CTEs are reusable
Difference #3: CTEs can be more readable
One More Difference: CTEs Must Be Named

When would you use a CTE?

A CTE (Common Table Expression) defines a temporary result set which you can then use in a SELECT statement. It becomes a convenient way to manage complicated queries. You define Common Table Expressions using the WITH statement. You can define one or more common table expression in this fashion.

Can I use subquery in CTE?

A CTE can reference itself, a subquery cannot. A CTE can reference other CTEs within the same WITH clause (Nest). A subquery cannot reference other subqueries. A CTE can be referenced multiple times from a calling query.

Can we use CTE in view?

A Common Table Expression, also called as CTE in short form, is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. The CTE can also be used in a View.

 A subquery is simply a select statement, that returns a single value and can be nested inside a SELECT, UPDATE, INSERT, or DELETE statement. 

It is also possible to nest a subquery inside another subquery.

According to MSDN, subqueries can be nested upto 32 levels.

Subqueries are always encolsed in paranthesis and are also called as inner queries, and the query containing the subquery is called as outer query.

The columns from a table that is present only inside a subquery, cannot be used in the SELECT list of the outer query.

1) Non-Corelated Subquery

sub query is executed first and only once. The sub query results are then used by the outer query. A non-corelated subquery can be executed independently of the outer query.

Select [Id], [Name], [Description]
from tblProducts
where Id not in (Select Distinct ProductId from tblProductSales)

2) CoRelated SubQuery

If the subquery depends on the outer query for its values, then that sub query is called as a correlated subquery. In the where clause of the subquery below, "ProductId" column get it's value from tblProducts table that is present in the outer query. So, here the subquery is dependent on the outer query for it's value, hence this subquery is a correlated subquery. Correlated subqueries get executed, once for every row that is selected by the outer query. Corelated subquery, cannot be executed independently of the outer query.

Select [Name],
(Select SUM(QuantitySold) from tblProductSales where ProductId = tblProducts.Id) as TotalQuantity
from tblProducts
order by Name

Introduction to SQL Server recursive CTE

A recursive common table expression (CTE) is a CTE that references itself. By doing so, the CTE repeatedly executes, returns subsets of data, until it returns the complete result set.

A recursive CTE is useful in querying hierarchical data such as organization charts where one employee reports to a manager or multi-level bill of materials when a product consists of many components, and each component itself also consists of many other components.

The following shows the syntax of a recursive CTE:

WITH expression_name (column_list)
AS
(
    -- Anchor member
    initial_query  
    UNION ALL
    -- Recursive member that references expression_name.
    recursive_query  
)
-- references expression name
SELECT *
FROM   expression_name
Code language: SQL (Structured Query Language) (sql)

In general, a recursive CTE has three parts:

1. An initial query that returns the base result set of the CTE. The initial query is called an anchor member.
2. A recursive query that references the common table expression, therefore, it is called the recursive member. The recursive member is union-ed with the anchor member using the UNION ALL operator.
3. A termination condition specified in the recursive member that terminates the execution of the recursive member.

The execution order of a recursive CTE is as follows:

• First, execute the anchor member to form the base result set (R0), use this result for the next iteration.
• Second, execute the recursive member with the input result set from the previous iteration (Ri-1) and return a sub-result set (Ri) until the termination condition is met.
• Third, combine all result sets R0, R1, … Rn using UNION ALL operator to produce the final result set.

The following flowchart illustrates the execution of a recursive CTE:

SQL Server Recursive CTE examples

Let’s take some examples of using recursive CTEs

A) Simple SQL Server recursive CTE example

This example uses a recursive CTE to returns weekdays from Monday to Saturday:

WITH cte_numbers(n, weekday) 
AS (
    SELECT 
        0, 
        DATENAME(DW, 0)
    UNION ALL
    SELECT    
        n + 1, 
        DATENAME(DW, n + 1)
    FROM    
        cte_numbers
    WHERE n < 6
)
SELECT 
    weekday
FROM 
    cte_numbers;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

In this example:

The DATENAME() function returns the name of the weekday based on a weekday number.

The anchor member returns the Monday

SELECT 
    0, 
    DATENAME(DW, 0)
Code language: SQL (Structured Query Language) (sql)

The recursive member returns the next day starting from the Tuesday till Sunday.

    SELECT    
        n + 1, 
        DATENAME(DW, n + 1)
    FROM    
        cte_numbers
    WHERE n < 6
Code language: SQL (Structured Query Language) (sql)

The condition in the WHERE clause is the termination condition that stops the execution of the recursive member when n is 6

n < 6
Code language: SQL (Structured Query Language) (sql)

B) Using a SQL Server recursive CTE to query hierarchical data

See the following sales.staffs table from the sample database:

In this table, a staff reports to zero or one manager. A manager may have zero or more staffs. The top manager has no manager. The relationship is specified in the values of the manager_id column. If a staff does not report to any staff (in case of the top manager), the value in the manager_id is NULL.

This example uses a recursive CTE to get all subordinates of the top manager who does not have a manager (or the value in the manager_id column is NULL):

WITH cte_org AS (
    SELECT       
        staff_id, 
        first_name,
        manager_id
        
    FROM       
        sales.staffs
    WHERE manager_id IS NULL
    UNION ALL
    SELECT 
        e.staff_id, 
        e.first_name,
        e.manager_id
    FROM 
        sales.staffs e
        INNER JOIN cte_org o 
            ON o.staff_id = e.manager_id
)
SELECT * FROM cte_org;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this example, the anchor member gets the top manager and the recursive query returns subordinates of the top managers and subordinates of the top manager, and so on.

In this tutorial, you have learned how to use the SQL Server recursive CTE to query hierarchical data.

Ref:
https://www.sqlservertutorial.net/sql-server-basics/sql-server-recursive-cte/#:~:text=An%20initial%20query%20that%20returns,using%20the%20UNION%20ALL%20operator.

Yes & No, depending on the number of base tables, the CTE is created upon, and the number of base tables affected by the UPDATE statement.

1. A CTE is based on a single base table, then the UPDATE suceeds and works as expected.
2. A CTE is based on more than one base table, and if the UPDATE affects multiple base tables, the update is not allowed and the statement terminates with an error.
3. A CTE is based on more than one base table, and if the UPDATE affects only one base table, the UPDATE succeeds(but not as expected always)