loading..
   English
09:39

Common table expressions (CTE) page 1

Let's start with an example to clarify the appointment of common table expressions.

Find the maximum amount of income/outcome among all 4 tables in the database "Recycled materials company, as well as the type of operation, date and point of reception, when and where it was recorded.

The task can be solved, the following way:

Console
Execute
  1. SELECT inc AS max_sum, type, date, point FROM (
  2. SELECT inc, 'inc' type, date, point FROM Income
  3. UNION ALL
  4. SELECT inc, 'inc' type, date, point FROM Income_o
  5. UNION ALL
  6. SELECT out, 'out' type, date, point FROM Outcome_o
  7. UNION ALL
  8. SELECT out, 'out' type, date, point FROM Outcome ) X
  9. WHERE inc >= ALL( SELECT inc FROM Income
  10. UNION ALL SELECT inc FROM Income_o
  11. UNION ALL SELECT out FROM Outcome_o
  12. UNION ALL SELECT out FROM Outcome );

First, we combine all available information, and then select only those rows for which the sum not less than each of the sums of the same query of 4 tables. In fact, we have twice written the code of union of four tables. How do I avoid this? You can create a view, and then address a query to him:

  1. CREATE VIEW Inc_Out AS
  2. SELECT inc, 'inc' type, date, point FROM Income
  3. UNION ALL
  4. SELECT inc, 'inc' type, date, point FROM Income_o
  5. UNION ALL SELECT out, 'out' type, date, point
  6. FROM Outcome_o
  7. UNION ALL SELECT out, 'out' type,date, point FROM Outcome;
  8. GO
  9. SELECT inc AS max_sum, type, date, point
  10. FROM Inc_Out WHERE inc >= ALL( SELECT inc FROM Inc_Out);

So, CTE plays the role of view, which is created within a single query and not stored as an object of the schema. The previous solution can be rewritten with the CTE as follows:

 
Console
Execute
  1. WITH Inc_Out AS (
  2. SELECT inc, 'inc' type, date, point FROM Income
  3. UNION ALL SELECT inc, 'inc' type, date, point FROM Income_o
  4. UNION ALL SELECT out, 'out' type, date, point FROM Outcome_o
  5. UNION ALL SELECT out, 'out' type,date, point FROM Outcome )
  6. SELECT inc AS max_sum, type, date, point FROM Inc_Out
  7. WHERE inc >= ALL( SELECT inc FROM Inc_Out);
 

As you can see, all similar to view except for the using of obligatory parentheses to restriction of query, formally, we need only replace CREATE VIEW with WITH. As for view, the column list can be indicated in parentheses after the name of CTE, if we need to include them not all from query and/or rename.

For example, (I add additional minimal sum to previous query)

 
Console
Execute
  1. WITH Inc_Out(m_sum, type, date, point) AS (
  2. SELECT inc, 'inc' type, date, point FROM Income
  3. UNION ALL SELECT inc, 'inc' type, date, point FROM Income_o
  4. UNION ALL SELECT out, 'out' type, date, point FROM Outcome_o
  5. UNION ALL SELECT out, 'out' type,date, point FROM Outcome )
  6. SELECT 'max' min_max,* FROM Inc_Out WHERE m_sum >= ALL(
  7. SELECT m_sum FROM Inc_Out)
  8. UNION ALL SELECT 'min', * FROM Inc_Out WHERE m_sum <= ALL(
  9. SELECT m_sum FROM Inc_Out);
 

Common table expressions can significantly reduce the amount of code, if repeatedly had to turn to the same derived tables. Note that the CTE can be used not only with the operator SELECT, but also with other operators of the DML language. Let's solve the following task: Need to send the passengers of flight 7772 on November 11, 2005 by another closest flight that departing later in the same day in the same destination. Ie this task is to update the records in the table Pass_in_trip. I shall not provide a solution to this task, which does not use the CTE, but you can do it for themselves, to compare the amount of code of two solutions.

Suggested exercises: 24, 116

Bookmark and Share
Pages 1 2 3

Content:

Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100