The CTE (common table expression) was
introduced into standard SQL 2005 in
order to simplify various classes of SQL Queries for which a derived table just wasn't suitable.
CTE
can use as a temporary result set that is defined within the execution scope of
a single SELECT, INSERT, UPDATE, DELETE,
or CREATE VIEW statement.
A CTE is similar to a derived table in that
it is not stored as an object and
lasts only for the duration of the query. Unlike a derived table, a CTE can be
self-referencing and can be referenced multiple times in the same query.
In
addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.
You define CTEs by adding a WITH clause directly before your
SELECT, INSERT, UPDATE, DELETE, or MERGE statement. The WITH clause can include
one or more CTEs, as shown in the following syntax:
[WITH <common_table_expression>
[,...]]
cte_name [(column_name [,...])]
AS (cte_query)
SQL Server supports two types of CTEs—recursive and non-recursive.
A non-recursive CTE is one that does not reference itself within the CTE.
Recursive CTE is the
process in which the query executes
itself.
--Non-recursive
WITH
cteTotSales (EmployeeID, TotSales)
AS
(
SELECT EmployeeID, SUM(UnitPrice*OrderQty)
FROM SalesOrder
GROUP BY EmployeeID
)
SELECT
e.FirstName + ' ' + e.LastName AS FullName, ts.TotSales
FROM Employees AS
e
INNER JOIN cteTotSales AS ts
ON e.EmployeeID = ts.EmployeeID
ORDER BY ts.TotSales DESC
Here, cteTotSales is ETE name, with two column then select query which
will fill data in the cteTotSales, then we can refer it as table with other
table as we did in the above query.
--recursive
WITH
Emp_CTE AS (
SELECT
EmployeeID, FirstName, LastName, ManagerID
FROM
Employees WHERE
ManagerID IS NULL
UNION ALL
SELECT
e.EmployeeID, e.FirstName, e.LastName, e.ManagerID
FROM
Employees e
INNER
JOIN Emp_CTE e_cte ON e_cte.EmployeeID = e.ManagerID
)
SELECT * FROM Emp_CTE
In the above example Emp_CTE is a Common Expression Table; the record for the CTE is
derived by the first SQL query before UNION ALL.
The result of the query gives
you the EmployeeID which don’t have ManagerID.
Second query after UNION ALL is executed
repeatedly to get results and it will continue until it returns no rows. For
above e.g. Result will have EmployeeIDs which have ManagerID. This is obtained by joining CTE result with
Employee table on columns EmployeeID of CTE with ManagerID of table Employee.
This process is recursive and will continue till there is no ManagerID who
doesn’t have EmployeeID.
Today's Question: Find out
the difference between CTE and Temp Table?
?
ReplyDelete