Table with billion rows and 100 columns, can be represented with one short name. We can use the same logic and give a name to our SELECT statements. If we want to use those SELECT statements in a subquery, join or union, we can refer to them by their names. This is how we can reduce any complex statement to its building blocks. This will break complex logic to simple steps, where each step is easy to understand and manage.
CTEs are a way to name our SELECT statement, and then to use those names in the final SELECT, INSERT, DELETE , UPDATE or MERGE statement.
Syntax of Common Table Expression
Bellow we can see statement with two CTEs. We can have them as many as we want ( Name1, Name2, Name3, Name4 … ). Each CTE will give a custom name to some SELECT statement. Thanks to this, final statement (which can be SELECT, INSERT, DELETE, UPDATE, MERGE), will be short and simple.
WITH JOIN Name2 ON someCondition JOIN Name2 ON someOtherCondition; |
Not only CTE can break our logic into manageable elements, but it can also reduce repetition. We can write "SELECT * FROM Table2" once and then use it twice in the final statement. CTE will only improve readability of our statement and it will make it more concise. It will not improve performance of a statement.
Sample Tables
We will create two sample tables to use them in our CTEs.
CREATE TABLE Cte1 ( Letter CHAR, Number INTEGER ); |  |
Example with SELECT
WITH Name1 AS ( SELECT * FROM Cte1 ), Name2 AS ( SELECT * FROM Cte2 ) |  |
Example with INSERT
| Because two tables don't have equal rows, EXCEPT will return all the rows from the Cte1 table.  | We will use CTE to insert those rows into Cte2.  |
Example with DELETE
WITH | When we use DELETE, we always have to delete from the table, "DELETE FROM Cte2". It is not possible to create a CTE, and then to delete from that CTE, like "DELETE FROM ", expecting that server will delete from the underlining table. That means that we only can use CTE in the WHERE clause.  After deleting A1 and B2 from Cte2, our Cte2 table is back to its original state. |
Example with UPDATE
WITH | We will update all the letters in the table Cte2 where numbers are common for the both tables. Because both tables have numbers 1 and 2, that means that all the letters in the table Cte2 will be updated to "X".  Again, CTE can only be used in the WHERE clause. |
Example with MERGE
WITH MERGE INTO Cte1 | In MERGE statement, we can use CTE in the USING clause. Values from that CTE can be used to change values in the database table.  Our tables, Cte1 and Cte2 don't have common letters, so we don't have matches. That means that all the rows from our Cte2 table will be inserted into Cte1. |
Aliases
We can use CTE without providing aliases to its columns.WITH |  |
| If we want to, we can provide aliases in the SELECT statement. It is also possible to provide aliases after the name of the CTE. If both are provided, then the outside aliases will prevail ( Column1 and Column2, and not colLetter and colNumber ). WITH Name1 ( Column1, Column2 ) AS ( SELECT Letter AS colLetter, Number AS colNumber FROM Cte1 ) SELECT * FROM Name1; |  |
Nested CTEs
WITH | It is possible to reference one CTE from another CTE (Name2 is calling Name1 ).  |
WITH | With nesting, we must be sure that referenced CTE is already defined. If we reference some of the latter CTEs, then those CTEs will not be recognize ( Name1 cannot call Name2, because it is not already defined ).  |
ORDER BY
In MonetDB, it is possible to use ORDER BY inside of CTEs definitions. That sorting will propagate to the final result.
WITH |  |
Recursive CTEs
Recursive CTEs are not supported in MonetDB database. This is done deliberately because of performance concerns.