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 dropped or the session ends |
| Persistence | Not persistent, results not stored in the database | Stored in the database and its result can be used by multiple queries or even different sessions |
| Recursive CTEs | Supported | Not Supported |
| Speed (depending on the size of data) | CTEs |
Example
WITH top_selling_products (product, sales) AS ( SELECT product, SUM(quantity) as sales FROM orders GROUP BY product ORDER BY sales DESC LIMIT 3 ) SELECT product, sales FROM top_selling_products; In this example, the CTE top_selling_products is defined to return the top 3 products based on total sales. The final statement retrieves the product and sales.
Conclusion
Overall, CTEs can help simplify complex queries and improve readability and maintainability of your code. They also help to avoid repetitive code, and can improve the performance of your queries by reducing the need for subqueries.