← Back to Knowledge Base← Previous questionNext question →
ProgrammingBackend Developer

What does the WITH statement (Common Table Expressions, CTE) do? What are the advantages of using CTE compared to subqueries? Provide an example.

Pass interviews with Hintsage AI assistant

Answer

The WITH statement (or CTE — Common Table Expression) temporarily creates a result set that can be used in the main SELECT or even recursively. CTEs make queries easier to understand, improve readability, and allow for the reuse of intermediate results.

Advantages over regular subqueries:

  • Improve the readability of complex queries (easier to debug and maintain).
  • Recursive queries can be used.
  • Can refer to CTE by name multiple times instead of duplicating the same subquery.

Example:

WITH high_salary AS (
  SELECT id, name FROM employees WHERE salary > 100000
)
SELECT * FROM high_salary WHERE name LIKE 'A%';

Trick Question

"Is it important to specify MATERIALIZED/NOT MATERIALIZED when declaring a CTE? How can this affect performance?"

Answer: In PostgreSQL, you can explicitly specify MATERIALIZED (always compute the CTE once and store it, even if used multiple times) or NOT MATERIALIZED (the CTE will be inlined in the main query). Usually, the optimizer decides whether to materialize the CTE, but forcing it can sometimes significantly impact speed.

Example:

WITH high_salary AS MATERIALIZED (
  SELECT id, name FROM employees WHERE salary > 100000
)
SELECT * FROM high_salary WHERE name LIKE 'A%';

Story

In a large project, an analyst replaced all subqueries with CTEs, not knowing that in PostgreSQL prior to version 12, CTEs are always materialized. The result — queries slowed down by 2-3 times. After reverting to subqueries or upgrading to newer versions, the problem was resolved.

Story

A report with several levels of nested subqueries became unreadable for a colleague. It was rewritten using CTEs with meaningful names — discussion and support of the queries became significantly easier.

Story

Two CTEs with the same names caused confusion in a large script: a CTE was used before its declaration. As a result — a compilation error and significant time wasted searching for the cause.