Databases Intermediate

What is CTE (Common Table Expression)?

A temporary named result set defined within a SQL statement using the WITH clause, improving query readability and enabling recursion.

CTEs create named temporary tables within a query using WITH clause syntax. They improve readability by breaking complex queries into logical steps. Recursive CTEs can traverse hierarchical data like org charts or category trees.

Example: WITH active_users AS (SELECT * FROM users WHERE active = true) SELECT * FROM active_users WHERE created_at > '2024-01-01'. PostgreSQL CTEs are optimization barriers by default, though materialization can be controlled.

Related Terms

A database operation that inserts a new row if it does not exist, or updates the existing row if it does.

Connection Pool

A cache of database connections that can be reused, avoiding the overhead of creating new connections for each request.

Database Constraint

Rules enforced by the database to maintain data integrity, including NOT NULL, UNIQUE, CHECK, PRIMARY KEY, and FOREIGN KEY.

A virtual table defined by a SQL query that provides a simplified or restricted view of data from one or more tables.

A centralized repository optimized for analytical queries that integrates data from multiple operational sources.

A PostgreSQL maintenance operation that reclaims storage from dead tuples and updates statistics for the query planner.

View All Databases Terms →