7900 shaares
161 private links
161 private links
SQL table expressions are somewhat similar to functions in a regular programming language — they reduce the overall complexity.
You can write an unreadable sheet of code, or you can break the code into understandable individual functions and compose a program out of them.
You can build a tower of nested subqueries, or you can extract them into CTEs and reference from the main query.There is a myth that “CTEs are slow”. It came from old versions of PostgreSQL (11 and earlier), which always materialized CTE — calculated the full result of a table expression and stored it until the end of the query.
Ok. There are some rules:
- CTE runs on every request
- CTE splits the query code into multiple chunks
- instead of subquery, always use CTE for clarity