CTE or subquery: how to choose?

Do you need to name a step?

A short subquery is suitable for a comparison value or result used only once. A CTE makes a business step visible and facilitates the chaining of several transformations.

Neither alone guarantees better performance. The engine can optimize them differently; measurement on real data remains necessary.

The query explained

Start by defining what a row of the result should represent. This decision determines the necessary columns, groupings, and controls.

The following example isolates the main mechanism. Run it, observe the result, then change one clause at a time to understand its effect.

WITH totaux AS (
  SELECT SUM(chiffre_affaires) AS ca, SUM(couts) AS couts
  FROM ventes_mensuelles
)
SELECT ca, couts, ca - couts AS marge
FROM totaux;

Read the result like an analyst

The CTE totals correspond to a nameable step. The final query remains focused on the delivered indicator.

During construction, run the contents of each CTE separately to check its level of detail and totals.

• Name the responsibility for the step.
• Check the intermediate level of detail.
• Avoid decorative CTE.
• Observe the plan on costly queries.

Errors that distort the analysis

A query can be valid without correctly answering the question. The most costly errors are often silent: wrong perimeter, multiplied line or missing value interpreted as zero.

Before sharing the result, compare it to a small hand-calculated sample and keep the scope definition with the query.

• Cut each line into a CTE.
• Assume that a CTE always accelerates.
• Reuse an alias at the wrong level.
• Hide a complex join without controlling its result.

Get into practice

Build the global margin exercise with a CTE, then write a subquery version and compare readability.

The SQL simulator of SQL.tn only runs read queries on dummy data in your browser. You can try multiple writes and compare their results without installing any software.