In general, common table expressions are useful whenever a table expression must appear multiple times within a single query. The following typical situations are suited to common table expressions.
Queries that involve multiple aggregate functions.
Views within a procedure that must contain a reference to a program variable.
Queries that use temporary views to store a set of values.
This list is not exhaustive. You may encounter many other situations in which common table expressions are useful.
Common table expressions are useful whenever multiple levels of aggregation must appear within a single query. This is the case in the example used in the previous section. The task was to retrieve the department ID of the department that has the most employees. To do so, the count aggregate function is used to calculate the number of employees in each department and the MAX function is used to select the largest department.
A similar situation arises when writing a query to determine which department has the largest payroll. The SUM aggregate function is used to calculate each department's payroll and the MAX function to determine which is largest. The presence of both functions in the query is a clue that a common table expression may be helpful.
WITH DeptPayroll( DepartmentID, amt ) AS ( SELECT DepartmentID, SUM( Salary ) AS amt FROM Employees GROUP BY DepartmentID ) SELECT DepartmentID, amt FROM DeptPayroll WHERE amt = ( SELECT MAX( amt ) FROM DeptPayroll )
Sometimes, it can be convenient to create a view that contains a reference to a program variable. For example, you may define a variable within a procedure that identifies a particular customer. You want to query the customer's purchase history, and as you will be accessing similar information multiple times or perhaps using multiple aggregate functions, you want to create a view that contains information about that specific customer.
You cannot create a view that references a program variable because there is no way to limit the scope of a view to that of your procedure. Once created, a view can be used in other contexts. You can, however, use a common table expressions within the queries in your procedure. As the scope of a common table expression is limited to the statement, the variable reference creates no ambiguity and is thus permitted.
The following statement selects the gross sales of the various sales representatives in the SQL Anywhere sample database.
SELECT GivenName || ' ' || Surname AS sales_rep_name, SalesRepresentative AS sales_rep_id, SUM( p.UnitPrice * i.Quantity ) AS total_sales FROM Employees LEFT OUTER JOIN SalesOrders AS o INNER JOIN SalesOrderItems AS i INNER JOIN Products AS p WHERE OrderDate BETWEEN '2000-01-01' AND '2001-12-31' GROUP BY SalesRepresentative, GivenName, Surname;
The above query is the basis of the common table expression that appears in the following procedure. The ID number of the sales representative and the year in question are incoming parameters. As this procedure demonstrates, the procedure parameters and any declared local variables can be referenced within the WITH clause.
CREATE PROCEDURE sales_rep_total ( IN rep INTEGER, IN yyyy INTEGER ) BEGIN DECLARE StartDate DATE; DECLARE EndDate DATE; SET StartDate = YMD( yyyy, 1, 1 ); SET EndDate = YMD( yyyy, 12, 31 ); WITH total_sales_by_rep ( sales_rep_name, sales_rep_id, month, order_year, total_sales ) AS ( SELECT GivenName || ' ' || Surname AS sales_rep_name, SalesRepresentative AS sales_rep_id, month( OrderDate), year( OrderDate ), SUM( p.UnitPrice * i.Quantity ) AS total_sales FROM Employees LEFT OUTER JOIN SalesOrders o INNER JOIN SalesOrderItems i INNER JOIN Products p WHERE OrderDate BETWEEN StartDate AND EndDate AND SalesRepresentative = rep GROUP BY year( OrderDate ), month( OrderDate ), GivenName, Surname, SalesRepresentative ) SELECT sales_rep_name, monthname( YMD(yyyy, month, 1) ) AS month_name, order_year, total_sales FROM total_sales_by_rep WHERE total_sales = ( SELECT MAX( total_sales) FROM total_sales_by_rep ) ORDER BY order_year ASC, month ASC; END;
The following statement demonstrates how to call the above procedure.
CALL sales_rep_total( 129, 2000 );
Sometimes, it can be useful to store a particular set of values within a SELECT statement or within a procedure. For example, suppose a company prefers to analyze the results of its sales staff by thirds of a year, instead of by quarter. Since there is no built-in date part for thirds, as there is for quarters, it is necessary to store the dates within the procedure.
WITH thirds ( q_name, q_start, q_end ) AS ( SELECT 'T1', '2000-01-01', '2000-04-30' UNION SELECT 'T2', '2000-05-01', '2000-08-31' UNION SELECT 'T3', '2000-09-01', '2000-12-31' ) SELECT q_name, SalesRepresentative, count(*) AS num_orders, SUM( p.UnitPrice * i.Quantity ) AS total_sales FROM thirds LEFT OUTER JOIN SalesOrders AS o ON OrderDate BETWEEN q_start and q_end KEY JOIN SalesOrderItems AS i KEY JOIN Products AS p GROUP BY q_name, SalesRepresentative ORDER BY q_name, SalesRepresentative;
This method should be used with care, as the values may need periodic maintenance. For example, the above statement must be modified if it is to be used for any other year.
You can also apply this technique within procedures. The following example declares a procedure that takes the year in question as an argument.
CREATE PROCEDURE sales_by_third ( IN y INTEGER ) BEGIN WITH thirds ( q_name, q_start, q_end ) AS ( SELECT 'T1', YMD( y, 01, 01), YMD( y, 04, 30 ) UNION SELECT 'T2', YMD( y, 05, 01), YMD( y, 08, 31 ) UNION SELECT 'T3', YMD( y, 09, 01), YMD( y, 12, 31 ) ) SELECT q_name, SalesRepresentative, count(*) AS num_orders, SUM( p.UnitPrice * i.Quantity ) AS total_sales FROM thirds LEFT OUTER JOIN SalesOrders AS o ON OrderDate BETWEEN q_start and q_end KEY JOIN SalesOrderItems AS i KEY JOIN Products AS p GROUP BY q_name, SalesRepresentative ORDER BY q_name, SalesRepresentative; END; CALL sales_by_third (2000);