Subqueries in FROM clause (derived tables) in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a subquery in the FROM clause called?

A subquery in the FROM clause is called a derived table. It acts like a temporary table that you can query from.

beginner

Why use a derived table in a query?

Derived tables help break complex queries into smaller parts. They let you calculate or filter data first, then use that result as a table for further queries.

beginner

How do you give a name to a derived table?

You must give an alias (a temporary name) to a derived table using AS alias_name right after the subquery.

intermediate

Example: What does this query do?<br>

SELECT dt.category, COUNT(*) FROM (SELECT category FROM products WHERE price > 100) AS dt GROUP BY dt.category;

This query first selects products with price greater than 100 inside the derived table named dt. Then it counts how many products are in each category from that filtered list.

intermediate

Can a derived table contain joins or aggregations?

Yes! A derived table can include joins, aggregations, or any valid SELECT query. It acts like a normal table for the outer query.

What must you always provide when using a subquery in the FROM clause?

A A WHERE clause

B An alias name

C A GROUP BY clause

D An ORDER BY clause

A derived table must have an alias so the outer query can refer to it.

Which of these is true about derived tables?

A They act like temporary tables inside a query

B They can only select columns, no calculations allowed

C They must be created before the main query runs

D They cannot be used with GROUP BY

Derived tables are temporary tables created by subqueries in the FROM clause and used immediately by the main query.

What is the main benefit of using a derived table?

A To speed up the database server

B To permanently store data

C To avoid using indexes

D To simplify complex queries by breaking them into parts

Derived tables help organize complex queries by letting you work with intermediate results.

Can a derived table include a JOIN inside its subquery?

A Only INNER JOIN is allowed

B No, joins are not allowed in derived tables

C Yes, it can include joins

D Only LEFT JOIN is allowed

Derived tables can contain any valid SELECT query, including joins.

Which clause is NOT required inside a derived table?

A WHERE

B SELECT

C FROM

D AS alias

WHERE is optional inside the subquery; SELECT and FROM are required, and AS alias is required outside the subquery.

Explain what a derived table is and why you would use one in a SQL query.

Think about how you can use a small table inside a bigger query.

Describe the syntax and rules for writing a subquery in the FROM clause.

Focus on how the subquery looks and how the outer query uses it.