Subquery in FROM Clause: Derived Tables and Inline Views

Sometimes you need to query the result of another query — for instance, first calculating total billed amount per doctor, then filtering or sorting that summary further. A subquery placed in the FROM clause, called a derived table or inline view, makes this possible by treating the inner query's result as if it were a real, temporary table for the rest of the outer query.

Key Definitions

• Derived table (inline view): A subquery placed in the FROM clause, treated as a temporary, virtual table that the outer query can select from, filter, or join.
• Aliasing requirement: MySQL requires every derived table to have an alias, since the temporary result has no inherent name of its own.

What You'll Learn

• Define a derived table (inline view) and how it's created using a FROM subquery.
• Write a query that filters or sorts an already-aggregated result.
• Understand the requirement that every derived table must be aliased.
• Recognize real reporting scenarios that benefit from FROM subqueries.

Detailed Explanation

Suppose you first want to calculate each doctor's total billed amount, and then filter to show only doctors whose total exceeds a certain threshold. You could try to do this in a single flat query, but aggregate functions can't be directly reused in the same-level WHERE clause. Instead, wrap the aggregation in a subquery inside FROM: `FROM (SELECT doctor_id, SUM(amount) AS total_billed FROM ... GROUP BY doctor_id) AS doctor_totals`. Now doctor_totals behaves exactly like a real table, with a doctor_id column and a total_billed column, and the outer query can filter, sort, or join it just like any other table.

MySQL requires every derived table to be given an alias — omitting it causes a syntax error — because internally MySQL needs a name to reference the temporary result set. This pattern is extremely common in reporting: aggregate first, then filter, sort, or join the aggregated summary.

Visual Summary

A two-stage pipeline: [Inner Query: GROUP BY doctor_id, SUM(amount)] produces a virtual table labeled [doctor_totals (derived table)] with columns doctor_id and total_billed, which then flows into [Outer Query: SELECT * FROM doctor_totals WHERE total_billed > 2000].

Quick Reference

Term	Meaning
Derived table	A subquery used in FROM, treated as a temporary table
Inline view	Another name for the same concept — a subquery acting as a virtual table
Alias requirement	MySQL requires derived tables to be named, e.g. AS doctor_totals

SQL Example

CREATE TABLE departments (
  department_id   INT PRIMARY KEY AUTO_INCREMENT,
  department_name VARCHAR(100) NOT NULL
);

CREATE TABLE doctors (
  doctor_id       INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name     VARCHAR(100) NOT NULL,
  department_id   INT,
  salary          INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE patients (
  patient_id      INT PRIMARY KEY AUTO_INCREMENT,
  patient_name    VARCHAR(100) NOT NULL,
  city            VARCHAR(80)
);

CREATE TABLE appointments (
  appointment_id    INT PRIMARY KEY AUTO_INCREMENT,
  patient_id        INT,
  doctor_id         INT,
  appointment_date  DATE,
  status            VARCHAR(20),
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  FOREIGN KEY (doctor_id)  REFERENCES doctors(doctor_id)
);

CREATE TABLE bills (
  bill_id         INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id  INT,
  amount          DECIMAL(10,2),
  paid            BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
);

INSERT INTO departments VALUES
  (1, 'Cardiology'), (2, 'Orthopedics'), (3, 'Neurology'), (4, 'Dermatology');

INSERT INTO doctors (doctor_id, doctor_name, department_id, salary) VALUES
  (101, 'Dr. Verma', 1, 95000), (102, 'Dr. Iyer', 2, 78000),
  (103, 'Dr. Sen', 3, 120000), (104, 'Dr. Khan', 1, 88000);

INSERT INTO patients VALUES
  (201, 'Amit Rao', 'Pune'), (202, 'Neha Joshi', 'Mumbai'),
  (203, 'Karan Mehta', 'Delhi'), (204, 'Divya Nair', 'Pune');

INSERT INTO appointments VALUES
  (301, 201, 101, '2026-05-01', 'Completed'),
  (302, 202, 102, '2026-05-02', 'Completed'),
  (303, 203, 101, '2026-05-03', 'Cancelled'),
  (304, 204, 103, '2026-05-04', 'Completed');

INSERT INTO bills (appointment_id, amount, paid) VALUES
  (301, 1500.00, TRUE), (302, 2200.00, FALSE),
  (303, 800.00, TRUE), (304, 3000.00, TRUE);

-- Derived table: total billed amount per doctor, then filter the summary
SELECT doctor_totals.doctor_name, doctor_totals.total_billed
FROM (
  SELECT d.doctor_name, SUM(b.amount) AS total_billed
  FROM doctors d
  JOIN appointments a ON d.doctor_id = a.doctor_id
  JOIN bills b ON a.appointment_id = b.appointment_id
  GROUP BY d.doctor_name
) AS doctor_totals
WHERE doctor_totals.total_billed > 2000;

The inner query calculates total_billed per doctor by joining appointments with bills and grouping by doctor. This result is aliased as doctor_totals and treated as a temporary table by the outer query, which then filters it using a simple WHERE clause — something that couldn't be done directly on SUM(amount) in a single flat query without repeating the entire aggregation logic in HAVING.

Real-World Examples

• Sales dashboards use derived tables to first calculate monthly revenue per region, then rank or filter those regional summaries further.
• E-commerce analytics use derived tables to compute average order value per customer, then join that summary against a customer segmentation table.
• Financial reporting systems use derived tables to pre-aggregate transaction totals before applying complex multi-step filtering logic.

Common Mistakes to Avoid

• Forgetting to alias a derived table, causing a MySQL syntax error.
• Using a FROM subquery when a simpler HAVING clause would achieve the same filtering more directly.
• Referencing a derived table's column without qualifying it with the derived table's alias in complex queries.

Interview Questions

Q1. What is a derived table in SQL?

A derived table is a subquery placed in the FROM clause, treated as a temporary, virtual table that the outer query can select from, filter, sort, or even join with other tables.

Q2. Why must a derived table always have an alias in MySQL?

MySQL requires an alias because the derived table's result has no inherent table name — an alias gives the outer query a way to reference its columns, and omitting it causes a syntax error.

Q3. When would you use a FROM subquery instead of using HAVING to filter an aggregate?

A FROM subquery is often used when you need to reference the aggregated result multiple times, join it with other tables, or apply additional transformations that go beyond simple aggregate filtering, which HAVING alone cannot achieve as flexibly.

Practice MCQs

1. What is another name for a subquery used in the FROM clause?

1. Correlated subquery
2. Derived table or inline view
3. Scalar subquery
4. Multi-row subquery

Answer: B. Derived table or inline view

Explanation: A subquery in FROM is commonly called a derived table or inline view, since it acts as a temporary virtual table for the outer query.

2. What does MySQL require for every derived table?

1. A PRIMARY KEY
2. An alias
3. A FOREIGN KEY constraint
4. An index

Answer: B. An alias

Explanation: MySQL requires an alias for every derived table so the outer query has a way to reference its columns; omitting the alias causes a syntax error.

Quick Revision Points

• A subquery in FROM is called a derived table or inline view.
• MySQL requires every derived table to have an alias.
• Derived tables are ideal for filtering, sorting, or joining against an already-aggregated result.

Conclusion

• A FROM subquery creates a derived table, treated as a temporary table by the outer query.
• Derived tables must always be aliased in MySQL.
• This pattern is essential for filtering or joining against pre-aggregated summary data.

A subquery placed in the FROM clause, known as a derived table or inline view, lets the outer query treat the inner query's result as a temporary, real table — complete with its own columns that can be filtered, sorted, or joined. This is especially useful for two-stage reporting logic, such as first aggregating billed amounts per doctor and then filtering that summary further, something that can't be done directly against an aggregate function within a single flat WHERE clause. MySQL requires every derived table to carry an alias.