Readable Queries with WITH (CTEs)

This lesson covers writing more readable queries with the WITH clause (CTEs), so complex subqueries stay organized. It's for anyone searching "SQL WITH clause CTE tutorial."

Writing WITH name AS (SELECT ...) gives a complex subquery a name that the rest of the query can reference as many times as needed. This is called a CTE (common table expression), and it's great for breaking a long query into readable pieces.

The example names a "students who scored 80+" query high_scorers with WITH high_scorers AS (...), then references it directly with SELECT * FROM high_scorers. It's similar to a derived table, but notice how much more readable the syntax reads.

A common early mistake is not knowing when to reach for a CTE over a nested subquery — deeply nested subqueries get hard to read, while a CTE lets you write "compute this first, then use it to compute that" as a top-to-bottom sequence, which produces much more maintainable SQL. Whenever you want to break a complex aggregation into clear stages, CTEs are a genuinely powerful tool — and one that shows up constantly in real analytical queries specifically for the readability boost.