Common Table Expression
Introduction A Common Table Expression (CTE) is considered a temporary table variable that only exists within the scope of a single query statement ( SELECT, INSERT, UPDATE or DELETE ). It helps you encapsulate a complex query logic cluster into a variable for reuse immediately below. In Postgres, the WITH clause is used as the syntax keyword to define a CTE . Features The scope of existence is only within a single query statement. Stored data is typically processed in RAM (in-memory) or temporarily saved as a file if the data is too large. No need to clean up ( drop ) after execution, it will automatically disappear as soon as the statement finishes executing. A special feature is the support for recursion ( WITH RECURSIVE ), which is highly powerful when processing tree or hierarchical data structures. Use cases The following are suitable use cases for using a CTE including You want to write clean and readable code, breaking down complex JOIN statements into individual steps. Sm...