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

Recall & Review

beginner

What is a derived table in SQL?

A derived table is a subquery used inside the FROM clause that acts like a temporary table for the main query to use.

beginner

Why do we use subqueries in the FROM clause?

We use subqueries in the FROM clause to create temporary tables that simplify complex queries or to prepare data before filtering or joining.

beginner

How do you give a name to a derived table?

You give a derived table an alias right after the closing parenthesis of the subquery, like this: (SELECT ...) AS alias_name.

intermediate

Can a derived table be joined with other tables?

Yes, a derived table can be joined with other tables just like a regular table in the FROM clause.

intermediate

Write a simple example of a derived table that calculates average sales per product.

SELECT avg_sales.product_id, avg_sales.avg_amount FROM (SELECT product_id, AVG(amount) AS avg_amount FROM sales GROUP BY product_id) AS avg_sales;

What keyword is used immediately after a subquery in the FROM clause?

A AS

B WHERE

C JOIN

D GROUP BY

The alias keyword AS is used to name the derived table created by the subquery.

Which clause can contain a derived table?

A FROM

B WHERE

C ORDER BY

D HAVING

Derived tables are subqueries placed inside the FROM clause.

Can a derived table be used to filter data before joining?

A Only in MySQL

B Yes

C No

D Only with GROUP BY

Derived tables can prepare or filter data before joining with other tables.

What happens if you omit the alias for a derived table?

A Query runs but returns no rows

B Derived table is ignored

C Syntax error

D Alias is automatically assigned

SQL requires an alias for derived tables; omitting it causes a syntax error.

Which of these is a valid use of a derived table?

A SELECT * FROM users GROUP BY (SELECT id FROM users);

B SELECT * FROM users WHERE (SELECT id FROM users);

C SELECT * FROM users JOIN (WHERE id > 5);

D SELECT * FROM (SELECT id FROM users) AS u;

Option D correctly uses a subquery in the FROM clause with an alias.

Explain what a derived table is and why it is useful in SQL queries.

Think about how you might create a small table inside a bigger query.

Describe the steps to write a query using a subquery in the FROM clause with an alias.

Remember the alias is mandatory for the subquery in FROM.