SQL Server Queries: Table Expressions

Table expressions are subqueries that are used where a table is expected. There are two types of table expressions:

  • Derived tables
  • Common table expressions

The following subsections describe these two forms of table expressions.

1. Derived Tables

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 another clause is processed by the SQL translator before the alias name is known. Example 6.75 shows an attempt to use a column alias where another clause is processed before the alias name is known.

Example 6.75 (Example of an Illegal Statement)

Get all existing groups of months from the enter_date column of the works_on table:

USE sample;

SELECT MONTH(enter_date) as enter_month

FROM works_on

GROUP BY enter_month;

The result is

Message 207: Level 16, State 1, Line 4

  The invalid column ‘enter_month’

The reason for the error message is that the GROUP BY clause is processed before the corresponding SELECT list, and the alias name enter_month is not known at the time the grouping is processed.

By using a derived table that contains the preceding query (without the GROUP BY clause), you can solve this problem, because the FROM clause is executed before the GROUP BY clause, as shown in Example 6.76.

Example 6.76

USE sample;

SELECT enter_month

FROM (SELECT MONTH(enter_date) as enter_month

FROM works_on) AS m

GROUP BY enter_month;

The result is

Generally, it is possible to write a table expression any place in a SELECT statement where a table can appear. (The result of a table expression is always a table or, in a special case, an expression.) Example 6.77 shows the use of a table expression in a SELECT list.

Example 6.77

USE sample;

SELECT w.job, (SELECT e.emp_lname

 FROM employee e WHERE e.emp_no = w.emp_no) AS name

FROM works_on w

WHERE w.job IN(‘Manager’, ‘Analyst’);

The result is

2. Common Table Expressions

A common table expression (CTE) is a named table expression that is supported by Transact-SQL. There are two types of queries that use CTEs:

  • Nonrecursive queries
  • Recursive queries

The following sections describe both query types.

3. CTEs and Nonrecursive Queries

The nonrecursive form of a CTE can be used as an alternative to derived tables and views. Generally, a CTE is defined using the WITH statement and an additional query that refers to the name used in WITH.

Examples 6.78 and 6.79 use the AdventureWorks database to show how CTEs can be used in nonrecursive queries. Example 6.78 uses the “convenient” features, while Example 6.79 solves the same problem using a nonrecursive query.

Example 6.78

USE AdventureWorks;

SELECT SalesOrderlD

FROM Sales.SalesOrderHeader

WHERE TotalDue > (SELECT AVG(TotalDue)

FROM Sales.SalesOrderHeader

WHERE YEAR(OrderDate) = ‘2014’)

AND Freight > (SELECT AVG(TotalDue)

  FROM Sales.SalesOrderHeader

  WHERE YEAR(OrderDate) = ‘2014’)/2.5;

The query in Example 6.78 finds total dues whose values are greater than the average of all dues and whose freights are greater than 40 percent of the average of all dues. The main property of this query is that it is space-consuming, because an inner query has to be written twice. One way to shorten the syntax of the query is to create a view containing the inner query, but that is rather complicated because you would have to create the view and then drop it when you are done with the query. A better way is to write a CTE. Example 6.79 shows the use of the nonrecursive CTE, which shortens the definition of the query in Example 6.78.

Example 6.79

USE AdventureWorks;

WITH price_calc(year_2014) AS

(SELECT AVG(TotalDue)

FROM Sales.SalesOrderHeader

WHERE YEAR(OrderDate) = ‘2014’)

SELECT SalesOrderID

FROM Sales.SalesOrderHeader

WHERE TotalDue > (SELECT year_2014 FROM price_calc)

AND Freight > (SELECT year_2014 FROM price_calc)/2.5;

The syntax for the WITH clause in nonrecursive queries is

WITH cte_name (column_list) AS

( inner_query)

outer_query

cte_name is the name of the CTE that specifies a resulting table. The list of columns that belong to the table expression is written in brackets. (The CTE in Example 6.79 is called price_ calc and has one column, year_2014.) inner_query in the CTE syntax defines the SELECT statement, which specifies the result set of the corresponding table expression. After that, you can use the defined table expression in an outer query. (The outer query in Example 6.79 uses the CTE called price_calc and its column year_2014 to simplify the inner query, which appears twice.)

4. CTEs and Recursive Queries

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

(anchor_member

UNION ALL

recursive_member)

outer_query

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. The first query will be invoked only once, and it starts to accumulate the result of the recursion. The first operand of UNION ALL does not reference the CTE (see Example 6.80). This query is called the anchor query or seed.

The second query contains a reference to the CTE and represents the recursive portion of it. For this reason it is called the recursive member. In the first invocation of the recursive part, the reference to the CTE represents the result of the anchor query. The recursive member uses the query result of the first invocation. After that, the system repeatedly invokes the recursive part. The invocation of the recursive member ends when the result of the previous invocation is an empty set.

The UNION ALL operator joins the rows accumulated so far, as well as the additional rows that are added in the current invocation. (Inclusion of UNION ALL means that no duplicate rows will be eliminated from the result.)

Finally, outer query defines a query specification that uses the CTE to retrieve all invocations of the union of both members.

The table definition in Example 6.80 will be used to demonstrate the recursive form of CTEs.

Example 6.80

USE sample;
CREATE TABLE airplane
   (containing_assembly VARCHAR(10),
   contained_assembly VARCHAR(10),
   quantity_contained INT,
   unit_cost DECIMAL (6,2));
insert into airplane values ( ‘Airplane’, ‘Fuselage’,1, 10);
insert into airplane values ( ‘Airplane’, ‘Wings’, 1, 11);
insert into airplane values ( ‘Airplane’, ‘Tail’,1, 12);
insert into airplane values ( ‘Fuselage’, ‘Cockpit’, 1, 13);
insert into airplane values ( ‘Fuselage’, ‘Cabin’, 1, 14);
insert into airplane values ( ‘Fuselage’, ‘Nose’,1, 15);
insert into airplane values ( ‘Cockpit’, NULL, 1,13);
insert into airplane values ( ‘Cabin’, NULL, 1, 14);
insert into airplane values ( ‘Nose’, NULL, 1, 15);
insert into airplane values ( ‘Wings’, NULL,2, 11);
insert into airplane values ( ‘Tail’, NULL, 1, 12)

The airplane table contains four columns. The column containing_assembly specifies an assembly, while contained_assembly comprises the parts (one by one) that build the corresponding assembly. From Table 6-2 you can see that an airplane has three parts (fuselage, wings, and tail), and that its fuselage has three subparts (cockpit, cabin, and nose).

Suppose that the airplane table contains 11 rows, which are shown in Table 6-2.

(The INSERT statements in Example 6.80 insert these rows in the airplane table.)

Example 6.81 shows the use of the WITH clause to define a query that calculates the total costs of each assembly.

Example 6.81

USE sample;

WITH list_of_parts(assembly1, quantity, cost) AS

(SELECT containing_assembly, quantity_contained, unit_cost

FROM airplane

WHERE contained_assembly IS NULL

UNION ALL

SELECT a.containing_assembly, a.quantity_contained,

CAST(l.quantity*l.cost AS DECIMAL(6,2))

FROM list_of_parts l,airplane a

WHERE l.assembly1 = a.contained_assembly)

SELECT * FROM list_of_parts;

The WITH clause defines the CTE called list_of_parts, which contains three columns: assemblyl, quantity, and cost. The first SELECT statement in Example 6.81 will be invoked only once, to accumulate the results of the first step in the recursion process.

The SELECT statement in the last row of Example 6.81 displays the following result:

The first five rows in the preceding output show the result set of the first invocation of the anchor member of the query in Example 6.81. All other rows are the result of the recursive member (second part) of the query in the same example. The recursive member of the query will be invoked twice: the first time for the fuselage assembly and the second time for the airplane itself.

The query in Example 6.82 is used to get the costs for each assembly with all its subparts.

Example 6.82

USE sample;

WITH list_of_parts(assembly, quantity, cost) AS

(SELECT containing_assembly, quantity_contained, unit_cost

FROM airplane

WHERE contained_assembly IS NULL

UNION ALL

SELECT a.containing_assembly, a.quantity_contained,

CAST(l.quantity*l.cost AS DECIMAL(6,2))

FROM list_of_parts l,airplane a

WHERE l.assembly = a.contained_assembly )

SELECT assembly, SUM(quantity) parts, SUM(cost) sum_cost

FROM list_of_parts

GROUP BY assembly;

The output of the query in Example 6.82 is as follows:

There are several restrictions for a CTE in a recursive query:

  • The CTE definition must contain at least two SELECT statements (an anchor member and one recursive member) combined by the UNION ALL operator.
  • The number of columns in the anchor and recursive members must be the same.

(This is the direct consequence of using the UNION ALL operator.)

  • The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.
  • The FROM clause of the recursive member must refer only once to the name of the CTE.
  • The following options are not allowed in the definition part of a recursive member: SELECT DISTINCT, GROUP BY, HAVING, aggregation functions, TOP, and subqueries. (Also, the only join operation that is allowed in the query definition is an inner join.)

Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.

Leave a Reply

Your email address will not be published. Required fields are marked *