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:
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:
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:
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)
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.