CTE stands for Common Table Expression, it’s a feature in SQL that allows you to define a temporary result set that you can refer to within the context of a single SELECT, INSERT, UPDATE, or DELETE statement.
CTEs are similar to subqueries but are defined using the WITH clause and can be self-referencing and can be used multiple times in the same query.
The basic syntax for a CTE is as follows:
WITH table_expression_name (column1, column2, ...) AS (
SELECT ...
)
SELECT ...
FROM table_expression_name
Where table_expression_name is the reference name.
Key differences between Temporary Tables and CTEs
| Feature | CTEs | Temporary Tables |
|---|---|---|
| Syntax | WITH clause | CREATE TEMPORARY TABLE statement |
| Scope | Only visible to the query in which they are defined | Visible to the current session |
| Lifetime | Only available for the duration of the query | Remain in the database until they are explicitly… |
[gs-fb-comments]