Webbo3 Data Analysis Bootcamp · SQL Module · Lesson 5

Subqueries and HAVING: Nested Queries, Group Filtering, and the Critical Difference Between WHERE and HAVING

A deep-dive lesson on writing subqueries inside WHERE, SELECT, and FROM clauses; using HAVING to filter grouped results; understanding the execution order that separates WHERE from HAVING; and mastering aliases for readable, maintainable SQL.

So far you have learned to filter individual rows with WHERE, sort results with ORDER BY, and eliminate duplicates with DISTINCT. Those are the building blocks. This lesson is about combining blocks into structures. A subquery is a query inside another query. It lets you answer questions that cannot be answered in a single step, for example finding all customers who spent more than the company average, or listing products that have never been ordered. The HAVING clause is the partner to WHERE, but it operates on groups rather than rows, which means it is the only place you can filter based on aggregate functions like SUM and COUNT. Understanding when to use WHERE versus HAVING, and how to nest queries inside one another, is what elevates you from someone who writes SQL to someone who solves problems with SQL. This lesson also covers aliases, the AS keyword, which makes complex queries readable and self-documenting.

1. Subquery in the WHERE Clause

The most common place to use a subquery is inside a WHERE clause, where the inner query generates a value or a list of values that the outer query uses to filter rows. This pattern appears constantly in real-world reporting.

Subquery returning a single value. Suppose you want all orders with a total amount greater than the company-wide average order value. You cannot know that average in advance, so you calculate it with a subquery:

SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount > (
  SELECT AVG(total_amount) FROM orders
);

MySQL executes the inner query first, calculating the average total_amount across all orders. It then substitutes that single numeric value into the outer query's WHERE clause and returns only orders above that threshold. The subquery is enclosed in parentheses and returns exactly one row with one column. If it returned multiple rows, the > operator would fail with an error.

Subquery returning multiple values with IN. When the inner query returns a list of values, use IN instead of a comparison operator:

SELECT first_name, last_name, email
FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM orders
  WHERE total_amount > 100000
);

This returns all customers who have placed at least one order above one hundred thousand naira. The inner query generates a list of customer IDs, and the outer query uses IN to test membership in that list. This is cleaner and often more efficient than a JOIN for simple existence checks. The inner query runs once, produces a list, and the outer query filters against it.

Correlated subqueries: the inner query depends on the outer query. In the examples above, the inner query runs independently. A correlated subquery is different. It references a column from the outer query, which means it runs once for every row in the outer query:

SELECT c.first_name, c.last_name
FROM customers c
WHERE (
  SELECT COUNT(*) FROM orders o
  WHERE o.customer_id = c.customer_id
) > 5;

This returns customers who have placed more than five orders. Notice that o.customer_id = c.customer_id links the inner query to the outer query. For every customer row, MySQL runs the subquery to count that customer's orders. Correlated subqueries are powerful but can be slow on large tables because the inner query executes repeatedly. For performance-critical queries, a JOIN is usually preferred over a correlated subquery.

EXISTS: testing for existence without returning values. When you only need to know whether a matching row exists, EXISTS is more efficient than IN because it stops scanning as soon as it finds the first match:

SELECT first_name, last_name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
  AND o.total_amount > 100000
);

This returns customers who have placed at least one high-value order. The SELECT 1 inside EXISTS is conventional. EXISTS only checks for the existence of any row, so the actual columns selected inside the subquery do not matter. EXISTS is often faster than IN for large datasets because it does not need to build a complete list of matching values.

2. Subquery in the SELECT Clause

A subquery in the SELECT clause acts like a calculated column. It runs once for every row in the outer query and returns a single value that appears as an additional column in the result set. This is useful when you want to enrich a query with related data without joining an entire table.

Adding a calculated column with a scalar subquery. Suppose you want a list of customers with their total number of orders displayed alongside their name:

SELECT
  first_name,
  last_name,
  (
    SELECT COUNT(*) FROM orders o
    WHERE o.customer_id = c.customer_id
  ) AS order_count
FROM customers c;

The subquery in the SELECT clause must return exactly one row and one column. If it returns multiple rows, MySQL throws an error. The result is a column called order_count that shows, for each customer, how many orders they have placed. This pattern is elegant for simple enrichment, but like all correlated subqueries, it can be slow on large tables. For high-performance reporting, a JOIN with GROUP BY is usually better.

Subquery in SELECT for percentage calculations. You can also use a subquery to calculate a percentage relative to a total:

SELECT
  region,
  SUM(total_amount) AS region_revenue,
  (
    SELECT SUM(total_amount) FROM orders
  ) AS total_revenue,
  ROUND(
    SUM(total_amount) / (
      SELECT SUM(total_amount) FROM orders
    ) * 100, 2
  ) AS revenue_percentage
FROM orders
GROUP BY region;

This returns each region's revenue, the total company revenue, and the region's percentage contribution. The subquery (SELECT SUM(total_amount) FROM orders) runs twice here, once for the total_revenue column and once inside the percentage calculation. MySQL optimizers usually cache identical subqueries, but for clarity you might prefer to compute the total once in a variable or a JOIN. Still, this pattern demonstrates the flexibility of subqueries in SELECT.

3. Subquery in the FROM Clause

A subquery in the FROM clause creates a temporary derived table that you can query like any other table. This is one of the most powerful subquery patterns because it lets you pre-aggregate, pre-filter, or pre-transform data before joining it or selecting from it.

Creating a derived table for pre-aggregation. Suppose you want to find customers whose average order value exceeds the company-wide average. First, you need each customer's average order value. Then you need the company average. A subquery in FROM handles the first part:

SELECT
  c.first_name,
  c.last_name,
  customer_avg.avg_order_value
FROM customers c
JOIN (
  SELECT customer_id, AVG(total_amount) AS avg_order_value
  FROM orders
  GROUP BY customer_id
) AS customer_avg
ON c.customer_id = customer_avg.customer_id
WHERE customer_avg.avg_order_value > (
  SELECT AVG(total_amount) FROM orders
);

The subquery in the FROM clause groups orders by customer_id and calculates each customer's average order value. It is aliased as customer_avg and then joined to the customers table. The WHERE clause then filters to keep only customers whose average exceeds the company-wide average, calculated by another subquery. This pattern, pre-aggregate in a derived table, then join and filter, is the standard approach for multi-level aggregation problems.

Derived tables must have aliases. MySQL requires every derived table to have an alias, even if you do not reference it elsewhere in the query. The AS keyword is optional but recommended for clarity:

SELECT * FROM (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
) AS order_summary
WHERE order_count > 5;

Without the AS order_summary alias, MySQL returns a syntax error. The alias order_summary becomes the name of the temporary table for the duration of the query. You can reference its columns in the outer SELECT, WHERE, and ORDER BY clauses just like columns from a real table.

4. The HAVING Clause: Filtering After GROUP BY

The WHERE clause filters individual rows before any grouping occurs. The HAVING clause filters groups after aggregation has happened. This distinction is not just syntactic. It reflects the logical order in which MySQL processes your query. Understanding this order is essential for writing correct queries and for optimizing performance.

The logical execution order. MySQL processes a query in this sequence: FROM determines which tables to read. WHERE filters individual rows. GROUP BY groups the surviving rows. Aggregate functions like SUM and COUNT are calculated for each group. HAVING filters the groups based on those aggregates. SELECT chooses which columns and expressions to display. ORDER BY sorts the final result. This means WHERE operates on raw rows, and HAVING operates on grouped summaries. You cannot use an aggregate function in WHERE because the aggregation has not happened yet when WHERE runs. You cannot reference a column alias from SELECT in WHERE for the same reason, because SELECT runs after WHERE.

Basic HAVING syntax. To find product categories with total revenue above one million naira:

SELECT product_category, SUM(total_amount) AS category_revenue
FROM orders
GROUP BY product_category
HAVING SUM(total_amount) > 1000000;

The GROUP BY clause groups orders by category. The SUM function calculates total revenue per category. The HAVING clause then keeps only groups where that sum exceeds one million. If you tried to write WHERE SUM(total_amount) > 1000000, MySQL would reject the query with an error because SUM is an aggregate function and WHERE does not operate on aggregates.

HAVING with multiple aggregate conditions. You can combine multiple conditions in HAVING using AND and OR:

SELECT region, COUNT(*) AS order_count, AVG(total_amount) AS avg_order
FROM orders
GROUP BY region
HAVING COUNT(*) > 100
  AND AVG(total_amount) > 50000;

This returns regions that have more than one hundred orders AND an average order value above fifty thousand naira. Both conditions must be met. HAVING supports the same logical operators as WHERE, and the same rule about parentheses applies when mixing AND and OR.

HAVING without GROUP BY. Although uncommon, HAVING can be used without GROUP BY to filter the result of an aggregate over the entire table:

SELECT COUNT(*) AS total_orders
FROM orders
HAVING COUNT(*) > 1000;

This returns a single row with the total order count only if that count exceeds one thousand. If not, the query returns an empty result set. This pattern is rare in practice but demonstrates that HAVING operates on the single implicit group created when no explicit GROUP BY is present.

5. The Critical Difference Between WHERE and HAVING

This is one of the most tested concepts in SQL interviews and one of the most common sources of bugs in production queries. The difference is not just where the clause sits in the syntax. It is about what stage of query execution each clause controls.

WHERE filters rows before grouping. Use WHERE when your condition applies to individual rows, not to groups. For example, filtering orders to include only those from 2026:

SELECT product_category, SUM(total_amount)
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY product_category;

Here, WHERE removes all pre-2026 orders before the GROUP BY even runs. This is efficient because fewer rows need to be grouped. It is also logically correct because the date condition applies to each individual order, not to the category as a whole.

HAVING filters groups after aggregation. Use HAVING when your condition applies to the result of an aggregate function. For example, keeping only categories with total revenue above one million:

SELECT product_category, SUM(total_amount) AS category_revenue
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY product_category
HAVING SUM(total_amount) > 1000000;

This query combines both clauses correctly. WHERE filters individual orders to the year 2026. GROUP BY aggregates the surviving orders by category. HAVING then removes categories whose 2026 total is below one million. You could not move the HAVING condition into WHERE, because WHERE does not have access to SUM(total_amount). You could technically move the WHERE condition into HAVING, but that would be inefficient because HAVING runs after grouping, meaning all orders, including pre-2026 ones, would be grouped first and then discarded.

The performance implication. Filtering with WHERE is almost always more performant than filtering with HAVING because WHERE reduces the dataset size before the expensive grouping and aggregation operations occur. HAVING filters after the heavy lifting is done. A common mistake is to place row-level conditions in HAVING out of habit or confusion. For example, HAVING order_date >= '2026-01-01' is invalid because order_date is not available at the group level unless it is in the GROUP BY list. Even if it were, it would force MySQL to group far more rows than necessary. The rule is simple: if the condition does not involve an aggregate function, it belongs in WHERE.

Column aliases in HAVING but not in WHERE. Because of the execution order, HAVING can reference column aliases defined in SELECT, but WHERE cannot. This is a quirk of MySQL. In standard SQL, HAVING should not reference aliases either, but MySQL allows it as a convenience:

SELECT product_category, SUM(total_amount) AS category_revenue
FROM orders
GROUP BY product_category
HAVING category_revenue > 1000000;

Here, HAVING references the alias category_revenue defined in SELECT. This works in MySQL but not in all database systems. For portability, it is safer to repeat the aggregate expression in HAVING: HAVING SUM(total_amount) > 1000000. For this bootcamp, either approach is acceptable, but know that the alias approach may fail if you ever switch to PostgreSQL or SQL Server.

6. Aliases: AS for Columns and Tables

Aliases are temporary names you assign to columns or tables within a query. They do not change anything in the database itself. They only affect how the result is displayed or how the table is referenced within the query. Aliases are essential for readable, maintainable SQL, especially when queries grow complex.

Column aliases with AS. Use column aliases to give friendly names to calculated or aggregated columns:

SELECT
  first_name,
  last_name,
  CONCAT(first_name, ' ', last_name) AS full_name,
  YEAR(registration_date) AS registration_year
FROM customers;

The AS keyword is optional in MySQL. You can write CONCAT(first_name, ' ', last_name) full_name without AS, and it works identically. However, AS makes the intent explicit and improves readability. Always use AS in production code and in this bootcamp. For column aliases that contain spaces or special characters, enclose them in single or double quotes: 'Total Revenue' or "Total Revenue".

Table aliases with AS. Table aliases become indispensable when you join multiple tables or use derived tables. They shorten long table names and disambiguate columns that exist in multiple tables:

SELECT
  c.first_name,
  c.last_name,
  o.order_id,
  o.total_amount
FROM customers AS c
JOIN orders AS o ON c.customer_id = o.customer_id;

Here, c is an alias for customers and o is an alias for orders. Every column reference is prefixed with the alias, which makes it immediately clear which table each column comes from. This is not just for readability. It is required when both tables have a column with the same name, like customer_id. Without the prefix, MySQL throws an ambiguous column error.

Naming conventions for aliases. The standard convention is to use the first letter of the table name, or the first letter of each word for multi-word names. customers becomes c, order_items becomes oi, product_categories becomes pc. For derived tables, choose a descriptive name that explains what the subquery produces: customer_totals, monthly_summary, high_value_orders. Avoid single-letter aliases like a, b, x, y in complex queries because they become meaningless when you have six tables.

Self-joins require aliases. When you join a table to itself, aliases are mandatory because you need two distinct references to the same table:

SELECT
  e.first_name AS employee_name,
  m.first_name AS manager_name
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.employee_id;

Here, e represents employees in their role as employees, and m represents the same table in its role as managers. Without aliases, MySQL could not distinguish which instance of the employees table each column belongs to. Self-joins are common in organizational hierarchies, bill-of-materials structures, and any data with recursive relationships.

Using AI to Move Faster with Subqueries and HAVING

Subqueries and HAVING are where SQL transitions from syntax to problem-solving. AI can help you translate business problems into the right query structure, debug logic errors, and refactor inefficient patterns into optimized alternatives.

1. Translate business questions into subquery structure with natural language.
When a stakeholder asks, "Which customers have spent more than the average customer?" you can describe this to Copilot: "Write a MySQL query that returns customers whose total lifetime spending is greater than the company-wide average customer lifetime spending. Use a subquery." AI will likely suggest a derived table or a correlated subquery pattern. Your job is to verify that the subquery returns the correct average, that the comparison uses the right aggregate, SUM for lifetime spending versus AVG for average order value, and that the join or correlation links customer_id correctly.

2. Use AI to choose between subquery types.
Not sure whether to put your subquery in WHERE, SELECT, or FROM? Describe your goal and ask: "I need each customer's name alongside their total order count and total revenue. Should I use a subquery in SELECT, a derived table in FROM, or a JOIN? Explain the performance and readability trade-offs." AI will explain that a JOIN with GROUP BY is usually fastest for this case, while a subquery in SELECT is simpler for small datasets but slower for large ones. This helps you make an informed choice rather than defaulting to the first pattern that comes to mind.

3. Debug WHERE versus HAVING confusion with AI.
If your query returns unexpected results or throws an aggregate function error, paste the query and the error into an AI assistant: "MySQL says 'Invalid use of group function' in this query. Should I use WHERE or HAVING, and why?" AI will identify that you placed an aggregate like SUM or COUNT in a WHERE clause, explain the execution order, and rewrite the query with the aggregate moved to HAVING. This is faster than scrolling through documentation, and the explanation reinforces the conceptual understanding you need for interviews.

4. Generate test scenarios that exercise subquery edge cases.
Ask AI: "Generate five business questions about a retail database that require subqueries, and for each one, indicate whether the subquery should go in WHERE, SELECT, or FROM." You will get scenarios like "Find products never ordered" (NOT EXISTS in WHERE), "List each customer's total orders as a column" (scalar subquery in SELECT), and "Find the top 10 customers by revenue, then join with their region details" (derived table in FROM). Work through these manually, then compare your solutions to AI's suggestions. The gap between your attempt and the AI output is where learning happens.

5. Refactor verbose queries with AI assistance.
If you inherit a query with nested subqueries three levels deep, paste it into an AI assistant and ask: "Refactor this query to reduce nesting while preserving the exact output. Explain each change." AI might replace a correlated subquery with a JOIN, merge multiple derived tables into one, or suggest a Common Table Expression, CTE, which you will learn in a later lesson. Understanding the refactoring reasoning is more valuable than the refactored query itself, because it teaches you to recognize anti-patterns in your own code.

A habit worth building from this lesson onward: before writing any query with a subquery, sketch the logic in three steps. Step one, what does the inner query need to produce? Step two, where does that result need to sit in the outer query? Step three, does this condition apply to individual rows or to groups? If you can answer those three questions clearly, the SQL syntax almost writes itself. AI can help with the syntax, but only you can answer the logic.

Next lesson: JOINs, INNER JOIN, LEFT JOIN, and combining data from multiple tables.