Query Structure: Intermediate

Break complex queries into readable steps

A CTE starts with WITH, followed by a name and query definition. The main query then references the CTE by name.

The CTE active_set filters for active users. The main query then filters for IDs greater than 100. This separates filtering steps, making the logic easier to follow.

___ active_users ___ (
  SELECT
    id,
    name
  FROM users
  WHERE active = 1
)

SELECT
  id,
  name
FROM ___

With a subquery, the inner query is embedded directly in the WHERE clause. This works well for simple filters but becomes harder to read as complexity grows.

Predict exactly how your query runs

SQL does not execute in the order it reads. CTEs and subqueries fire before the main query, WHERE filters rows before SELECT picks columns, and HAVING runs after aggregation that WHERE cannot touch. The clause that appears first on screen is rarely the first to run. For developers used to procedural code where line 1 runs before line 2, this is the single biggest source of cryptic SQL errors. The CTE you just wrote is the clearest example: the CTE block ran before anything in the main SELECT was evaluated.

Which query correctly uses a column alias defined in SELECT?

SELECT
  category,
  AVG(price) AS avg_price
FROM products
GROUP BY category
___ ___(price) > 50

Remembering execution order prevents common errors like using aliases in WHERE or aggregate functions in WHERE instead of HAVING.

Nest queries to answer layered questions

Before CTEs became standard, all intermediate results had to be written inline as nested queries: a query inside another query's WHERE clause or FROM clause. Subqueries still appear constantly in production SQL and in interview questions, and they produce exactly the same results as an equivalent CTE. Understanding subqueries means you can read any SQL in the wild, write the embedded style when a full CTE block would be overkill, and recognize when a nested query is slowing things down enough to warrant refactoring.

Subqueries appear in three positions: inside WHERE, inside FROM, and inside SELECT. Each position serves a different purpose.

A table subquery replaces a table name in the FROM clause with a complete SELECT statement. The inner query runs first to produce an intermediate result, then the outer query filters from that result.

A scalar subquery returns a single value that can be used in the SELECT list or WHERE clause.

When a subquery returns multiple rows, use IN to check if a value matches any result.

SELECT
  order_id
FROM orders
WHERE user_id ___ (
  ___
    id
  FROM users
  WHERE ___ = 1
)

A non-correlated subquery runs once and shares its result with every row in the outer query. A correlated subquery references the outer query's current row, so it must re-execute for each row the outer query processes. On a table with one million rows, a correlated subquery runs one million times. This is why correlated subqueries can be slow and are often worth rewriting as a JOIN or CTE.

Inner First

Subqueries execute before the outer query

Performance

Large or repeated subqueries can slow things down

Temporary

Datasets exist only during query execution

Best Use

Small, one-off computations and filtering

Combine results from multiple queries

UNION operations stack multiple query results vertically, creating a single combined dataset from separate queries.

UNION operations combine results from multiple queries into a single dataset. Use them to merge datasets from different tables or sources. Understanding UNION DISTINCT vs. UNION ALL is important because duplicates and performance differ.

These examples show the key difference between preserving all rows with UNION ALL and deduplicating with UNION DISTINCT.

UNION ALL stacks two result sets on top of each other, keeping every row from both queries including any duplicates.

Combines rows from two tables. UNION ALL keeps duplicates. Columns must match in number and type.

Duplicates are removed. Writing UNION DISTINCT explicitly clarifies intent, but UNION without ALL or DISTINCT after it will perform a UNION DISTINCT operation, so clarity is best practice.

Knowing when to use UNION ALL versus UNION DISTINCT depends on your data requirements and performance needs.

Suppose you have separate logs from multiple environments and want to produce a single dataset for processing. UNION ALL preserves repeated device IDs, while UNION DISTINCT ensures only unique IDs are kept. Knowing which variant to use affects both the correctness and speed of your processing.

Sort and paginate result sets

ORDER BY sorts the rows in your result based on one or more columns. By default, sorting is in ascending order (smallest to largest, A to Z). Add DESC after the column name to sort in descending order (largest to smallest, Z to A).

LIMIT controls how many rows to return, which is essential for pagination and "top N" queries.

LIMIT restricts the number of rows returned. It is often used with ORDER BY to get the "top N" results, such as the top 5 customers or the 10 most recent orders.

This returns only the top 3 customers by total spending. Without LIMIT, you would see all customers.

See if you can combine ORDER BY and LIMIT to answer a common business question.

SELECT
  product_name,
  price
FROM products
___ price ___
___ ___

Combining ORDER BY with LIMIT is the standard pattern for "top N" queries, such as finding the five most expensive products or the ten most recent sign-ups.

Without ORDER BY, the database can return rows in any order it chooses, so LIMIT alone does not guarantee you get the highest or lowest values. It simply returns whichever rows the database finds first.

In large datasets, ORDER BY before LIMIT is much more efficient than sorting the entire result after the fact, because the database can often use an index to avoid scanning every row.