Subqueries in SELECT, FROM (Derived Tables), & WHERE Clauses
Subqueries are not restricted to the WHERE clause. Depending on your business requirements, subqueries can be placed in the SELECT clause (to compute inline scalar calculations), the FROM clause (to create temporary in-memory Derived Tables), or the HAVING clause.
1. Subqueries in the SELECT Clause (Scalar Expressions)
A subquery placed in the SELECT clause must be a Scalar Subquery (returning strictly one column and one row per outer record):
1-- Display each employee alongside the company average salary and deviation:
1-- List categories with an inline count of their products:
2SELECT
3 category_name,
4(SELECTCOUNT(*)FROM products p WHERE p.category_id = c.category_id)AS total_products
5FROM categories c;
2. Subqueries in the FROM Clause (Derived Tables)
When a subquery appears in the FROM clause, it acts as a temporary, virtual table during query execution. In SQL terminology, this is called a Derived Table (or Inline View).
[!CRITICAL]
The Golden Rule of Derived Tables in MySQL:
Every derived table in a FROM clause MUST HAVE ITS OWN TABLE ALIAS! Omitting the alias will trigger Error 1248: Every derived table must have its own alias.
1-- Correct Syntax: Note the mandatory alias 'AS dept_stats' at the end!
2SELECT
3 dept_stats.department_id,
4 dept_stats.avg_sal
5FROM(
6SELECT department_id,AVG(salary)AS avg_sal
7FROM employees
8GROUPBY department_id
9)AS dept_stats
10WHERE dept_stats.avg_sal >60000.00;
3. Advanced Example: Two-Stage Aggregations
SQL does not allow nesting aggregate functions directly (e.g., AVG(COUNT(*)) is illegal!). To calculate the "Average number of orders placed per customer", you must use a Derived Table:
1-- Stage 1: Inner query counts orders per customer.
2-- Stage 2: Outer query calculates the average of those counts!
You can use subqueries inside HAVING to filter groups dynamically:
1-- Find departments whose total payroll exceeds the Marketing department's payroll:
2SELECT department_id,SUM(salary)AS total_payroll
3FROM employees
4GROUPBY department_id
5HAVINGSUM(salary)>(
6SELECTSUM(salary)
7FROM employees
8WHERE department_id =(SELECT department_id FROM departments WHERE dept_name ='Marketing')
9);
5. Best Practices & Common Pitfalls
Performance of Subqueries in SELECT: If a scalar subquery in the SELECT clause is correlated with the outer table, it will re-execute for every single row in the outer query (10,000 outer rows = 10,000 subquery executions!). Prefer joining an aggregated derived table instead.
Derived Table Materialization: MySQL 8.0 optimizes derived tables by either merging them into the outer query or materializing them into a temporary memory table with automatic internal index lookup generation.
Interactive Quiz• Knowledge Check
Multiple Choice Questions
Question 1
What error occurs in MySQL if you execute a subquery in the FROM clause without assigning an alias?
Question 2
How can a developer calculate the average of an aggregate (e.g., the average order count per customer) in standard SQL?
Question 3
What constraint is strictly enforced on subqueries placed in the SELECT column list?
Question 4
What is another common name for a subquery placed in the FROM clause?
Question 5
Why can scalar subqueries in the SELECT clause degrade performance on large tables?
Next Lesson
Correlated Subqueries
Continue learning with hands-on practice, examples, and exercises in the upcoming topic.