Table query is a named query which is like a relational table which can be used like other tables .

The Microsoft SQL has four types of table expressions and we will go one by one :

1 – Derived tables

2 – CTE (common table expressions)

3 – Views

4 – Inline table valued Function (TVF)

1 – Derived Tables :

Derived tables are the queries which we write in the FROM clause of the outer query and there existence depends on the outer query . Once the outer query is finished , the derived table is finished.

AS we are using the TSQL2012 database , We can execute the

select * from (select custid,companyname from Sales.Customers) as customer

select * from (select custid,companyname from Sales.Customers where city like ‘%Berlin%’ ) as customer
select * from (select orderyear,count(distinct custid) as numcusts
from (select YEAR(orderdate) as orderyear, custid from Sales.Orders) as outer1
group by orderyear) as outer2
where outer2.numcusts > 70

Note : A query must have to meet the requirements of the table expression.

1. The rows returned might not have guaranteed order as the table expression is supposed to represent a relational table .

2 . All columns names in the table expression must be unique for instance : If we join two tables in the table expression and both the tables have columns with same name then we can have a problem. The easiest way is to give column aliases to these columns which have same name.

3. Assign column aliases: The best benefit of table expression is that you can refer the column aliases used in the select of inner query in the outer query. From this we can undertand that the column aliases used in the select clause cannot be used in the where or group by clause because select clause is logically processed after the where and group by clause in SQL .

For example : The following statement will give an error :

select YEAR(orderdate) as orderyear, custid from Sales.Orders) as outer1
group by orderyear)

Msg 207, Level 16, State 1, Line 5
Invalid column name ‘orderyear’.

We can solve this problem by using the YEAR(orderdate) in group by or where clause. But there can be possibility that there can be many expressions or inner queries. So to maintain code readability , we can just give the name to the query for the ease and use that name wherever needed.

SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM (SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders) AS D
GROUP BY orderyear;

Here we defined a derived table with name D on select year(orderdate) as orderyear , custid  which returns order year and customer ID of all rows . The outer query can refer to orderyear alias of inner query , in the Group By clause and in its select clause .

2 – Common Table Expressions (CTE):

CTE are another type of table expressions . They are defined by WITH statements, have a inner query defining CTE and the outer query which uses against it .

For instance :

WITH USAcustomers as

( select custid, companyname  FROM sales.customers where country = ‘USA’

)

select * from USAcustomers ;

 

We can also use column aliases in the outer query .

WITH C AS
(
select year(orderdate) as orderyear, custid 
from sales.orders
)

select orderyear , count(distinct custid) as numcasts
from C 
Group by orderyear;

Use the semicolon to end the CTE at the end of outer query.

Multiple CTEs

CTE has several advantages over derived tables. If you need to refer to one CTE from another we don’t end up nesting like we do in derived tables. Instead , when we define multiple CTE we define them by commas under the same WITH statement.

with c1 as 
(
select year(orderdate) as orderyear, custid
from sales.orders
),
c2 as 
(
select orderyear, count(distinct custid) as numcasts
from c1 group by orderyear
)
select orderyear, numcasts from c2 where numcasts > 70

This approach improves the readability and maintainability of the code compared to the nested derived table does.

 

Recursive CTEs:        

Recursive CTE has recursion capablities. Recursive CTE is defined by two queries. One is called Anchor member and one is called recursive member .

WITH <CTE_Name>[(<target_column_list>)]
AS
(
<anchor_member>
UNION ALL
<recursive_member>
)
<outer_query_against_CTE>;

The anchor member returns a valid result table and is invoked only once .

The recursive member is a query which has reference to the CTE name.

The reference to the CTE name is the result set which is returned by the previous execution. When the first time the recursive member is invoked , the previous result set is whatever the anchor member returned.

In the next invocations , the result set referenced to the CTE name has the result of the previous invocations of the recursive members. The recursive member is invoked repeatedly until it returns an empty set .

Both the queries must be having the same columns and their types.

The reference to the CTE name in the outer query is the unified result sets of the invocation of the anchor member and all invocations of the recursive member .

with EmployeeCTE as 
( select empid, mgrid, firstname, lastname
from hr.employees
where empid = 2 

union all

select c.empid,c.mgrid,c.firstname,c.lastname
from EmployeeCTE p 
inner join HR.Employees as c 
on c.mgrid = p.empid
)
select empid, mgrid,firstname, lastname
from EmployeeCTE

The anchor member queries the HR.Employees table and simply returns the row for employee 2.
SELECT empid, mgrid, firstname, lastname
FROM HR.Employees
WHERE empid = 2

The recursive member joins the CTE—representing the previous result set—with the Employees table to return the direct subordinates of the employees returned in the previous result set.

SELECT C.empid, C.mgrid, C.firstname, C.lastname
FROM EmployeeCTE AS P
JOIN HR.Employees AS C
ON C.mgrid = P.empid

In other words, the recursive member is invoked repeatedly, and in each invocation it returns the next level of subordinates. The first time the recursive member is invoked, it returns the direct subordinates of employee 2—employees 3 and 5. The second time the recursive member is invoked, it returns the direct subordinates of employees 3 and 5—employees 4, 6, 7, 8, and 9. The third time the recursive member is invoked, there are no more subordinates; the recursive member returns an empty set, and therefore recursion stops.