SQL Subqueries and CTEs

Some questions can’t be answered in one step. “Which laptops cost more than average?” needs the average first, and only then the comparison. SQL lets you nest one query inside another for exactly this, and that inner query is called a subquery.

A CTE (Common Table Expression) does the same job in a more readable way. You name the inner query up front and then use it like a temporary table.

Subquery

Here is a laptops table.

SELECT model, price
FROM laptops
WHERE price > (SELECT AVG(price) FROM laptops);

Output:

Code Explanation:

• The bracketed query runs first and returns one number: the average price, about 70833.
• The outer query then keeps only the rows priced above that number.
• Core Max qualifies by a small margin, at 71000.

A subquery can also sit in the FROM clause, where it acts as a temporary table, or inside IN (...) to filter against a list of values.

CTE  (Common Table Expression)

Now a harder question: which laptops cost more than the average of their own brand? That needs a brand-level average to be built first.

WITH brand_avg AS (
    SELECT brand, ROUND(AVG(price), 0) AS avg_price
    FROM laptops
    GROUP BY brand
)
SELECT l.model, l.brand, l.price, b.avg_price
FROM laptops AS l
JOIN brand_avg AS b ON l.brand = b.brand
WHERE l.price > b.avg_price;

Output:

Code Explanation:

• WITH brand_avg AS (...) builds a small result with one average price per brand and gives it a name.
• The main query joins that named result back to laptops on the brand column.
• WHERE keeps only the laptops priced above their own brand’s average.

Which One to Pick

Use a subquery for a quick, single-use value. Use a CTE when the logic has steps, or when you need the same intermediate result more than once.

You can chain several CTEs, separated by commas, and each one can use the ones before it. Long queries become far easier to read this way.