# Subqueries & CTEs: Two Ways to Query Inside a Query

Subqueries and Common Table Expressions (CTEs) are two methods in SQL to query within a query. They can both solve similar problems, but they have distinct differences in how they appear and how they work. Subqueries are nested inside another query, enclosed in parentheses. They can return a single value, a list of values, or even an entire table-like result.

A subquery can appear in various parts of a query, such as the SELECT list, the FROM clause, or the WHERE clause. In contrast, a CTE is defined at the beginning of a query using the WITH clause, given a name, and then referenced later in the query, just like a regular table. It exists only for the duration of that specific query and disappears once the query finishes.

One key difference between subqueries and CTEs is their readability and reusability. While subqueries can get complex when nested deeply and may become hard to read, CTEs remain clear even when chained together with multiple steps. Additionally, subqueries cannot reference themselves, but CTEs can be recursive, allowing them to reference themselves for hierarchical data.

In terms of performance, modern databases typically optimize both subqueries and CTEs similarly, so the choice between them often comes down to readability and reusability rather than speed. When deciding which to use, consider the simplicity of the logic and whether it needs to be reused. Use subqueries for small, single-use queries.

Opt for CTEs when you need to name a step, reuse it, chain several steps together, or walk a hierarchy recursively. The easiest way to remember the difference is that a subquery is a query hidden inside another query, while a CTE is a query given a name up front and then used like a table below it. As queries become more complex and start nesting deeper than three levels, it's usually a sign to extract them into a CTE.