Subqueries in FROM (derived tables) in PostgreSQL

Introduction

Subqueries in FROM let you use the result of one query as a temporary table inside another query. This helps break complex problems into smaller parts.

When you want to calculate a summary first, then filter or join on it.

When you need to reuse a complex calculation multiple times in a query.

When you want to organize your query for better readability.

When you want to join aggregated data with detailed data.

When you want to isolate a part of the query to test or debug it separately.

Syntax

PostgreSQL

SELECT columns
FROM (subquery) AS alias
WHERE conditions;

The subquery inside FROM must have an alias (a temporary name).

The subquery acts like a temporary table you can select from.

Examples

This example finds departments with average salary above 50000 by first calculating averages in a subquery.

PostgreSQL

SELECT avg_salary
FROM (SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department) AS dept_avg
WHERE avg_salary > 50000;

This example joins the average salary per department with the employee details.

PostgreSQL

SELECT d.department, d.avg_salary, e.employee_name
FROM (SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department) AS d
JOIN employees e ON d.department = e.department;

Sample Program

This creates a simple employees table, inserts data, then uses a subquery in FROM to find departments with average salary above 50000.

PostgreSQL

CREATE TABLE employees (
  employee_id SERIAL PRIMARY KEY,
  employee_name VARCHAR(50),
  department VARCHAR(50),
  salary INT
);

INSERT INTO employees (employee_name, department, salary) VALUES
('Alice', 'Sales', 60000),
('Bob', 'Sales', 55000),
('Charlie', 'HR', 45000),
('Diana', 'HR', 47000),
('Eve', 'IT', 70000);

SELECT department, avg_salary
FROM (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept_avg
WHERE avg_salary > 50000;

Important Notes

Always give an alias to your subquery in FROM, or the query will fail.

Subqueries in FROM can be nested multiple levels if needed.

Using subqueries in FROM can improve query clarity and modularity.

Summary

Subqueries in FROM act like temporary tables inside your main query.

They help break down complex queries into simpler parts.

Always remember to give your subquery an alias.