New sql features

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

SELECT inc AS max_sum, type, date, point FROM ( 
  SELECT inc, 'inc' type, date, point FROM Income 
  UNION ALL
  SELECT inc, 'inc' type, date, point FROM Income_o 
  UNION ALL 
  SELECT out, 'out' type, date, point FROM Outcome_o 
  UNION ALL 
  SELECT out, 'out' type, date, point FROM Outcome ) X 
    WHERE inc >= ALL( SELECT inc FROM Income 
       UNION ALL SELECT inc FROM Income_o 
       UNION ALL SELECT out FROM Outcome_o 
       UNION ALL SELECT out FROM Outcome );

SELECT inc as max_sum, type, date, point FROM (   SELECT inc, 'inc' type, date, point FROM Income   UNION ALL   SELECT inc, 'inc' type, date, point FROM Income_o   UNION ALL   SELECT out, 'out' type, date, point FROM Outcome_o   UNION ALL   SELECT out, 'out' type, date, point FROM Outcome ) X     WHERE inc >= ALL( SELECT inc FROM Income        UNION ALL SELECT inc FROM Income_o        UNION ALL SELECT out FROM Outcome_o        UNION ALL SELECT out FROM Outcome );

    Scheme

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:

CREATE VIEW Inc_Out AS 
  SELECT inc, 'inc' type, date, point FROM Income 
  UNION ALL 
  SELECT inc, 'inc' type, date, point FROM Income_o 
  UNION ALL SELECT out, 'out' type, date, point 
     FROM Outcome_o 
  UNION ALL SELECT out, 'out' type,date, point FROM Outcome; 
GO 
SELECT inc AS max_sum, type, date, point 
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

 
WITH Inc_Out AS ( 
  SELECT inc, 'inc' type, date, point FROM Income 
  UNION ALL SELECT inc, 'inc' type, date, point FROM Income_o 
  UNION ALL SELECT out, 'out' type, date, point FROM Outcome_o 
  UNION ALL SELECT out, 'out' type,date, point FROM Outcome ) 
SELECT inc AS max_sum, type, date, point FROM Inc_Out 
WHERE inc >= ALL( SELECT inc FROM Inc_Out);

  WITH Inc_Out AS (   SELECT inc, 'inc' type, date, point FROM Income   UNION ALL SELECT inc, 'inc' type, date, point FROM Income_o   UNION ALL SELECT out, 'out' type, date, point FROM Outcome_o   UNION ALL SELECT out, 'out' type,date, point FROM Outcome ) SELECT inc as max_sum, type, date, point FROM Inc_Out WHERE inc >= ALL( SELECT inc FROM Inc_Out);

    Scheme

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

 
WITH Inc_Out(m_sum, type, date, point) AS ( 
  SELECT inc, 'inc' type, date, point FROM Income 
  UNION ALL SELECT inc, 'inc' type, date, point FROM Income_o 
  UNION ALL SELECT out, 'out' type, date, point FROM Outcome_o 
  UNION ALL SELECT out, 'out' type,date, point FROM Outcome ) 
SELECT 'max' min_max,* FROM Inc_Out WHERE m_sum >= ALL( 
  SELECT m_sum FROM Inc_Out) 
  UNION ALL SELECT 'min', * FROM Inc_Out WHERE m_sum <= ALL(
         SELECT m_sum FROM Inc_Out); 
 

  WITH Inc_Out(m_sum, type, date, point) AS (   SELECT inc, 'inc' type, date, point FROM Income   UNION ALL SELECT inc, 'inc' type, date, point FROM Income_o   UNION ALL SELECT out, 'out' type, date, point FROM Outcome_o   UNION ALL SELECT out, 'out' type,date, point FROM Outcome ) SELECT 'max' min_max,* FROM Inc_Out WHERE m_sum >= ALL(   SELECT m_sum FROM Inc_Out)   UNION ALL SELECT 'min', * FROM Inc_Out WHERE m_sum <= ALL(          SELECT m_sum FROM Inc_Out);  

    Scheme

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.

Content:

Recursive CTE