Subqueries By structure FROM · inline view · derived table

Inline Subquery (Inline View)

A subquery placed in the FROM clause and treated as a temporary table by the outer query. Perfect for pre-aggregating data before joining or filtering further.

What an inline subquery is

An inline subquery — also called an inline view or derived table — is a subquery that lives in the FROM clause. Instead of returning a value or a set, it returns a whole result set that the outer query treats as if it were a table.

The outer query joins, filters, groups, or selects from this on-the-fly table. It's a powerful way to break a complex query into a clean two-step pipeline: "first compute this intermediate set, then process it."

Syntax

SELECT column_list
FROM (
    SELECT ...
    FROM source_table
    WHERE ...
) AS alias_name
[JOIN ... | WHERE ...];

📌 The alias is required. PostgreSQL won't let an inline subquery sit in FROM without a name — pick something short like t or descriptive like dept_summary.

Demo dataset — employees

SQL — Create & insert

CREATE TABLE employees (
    employee_id    INT PRIMARY KEY,
    first_name     VARCHAR(50),
    last_name      VARCHAR(50),
    email          VARCHAR(100),
    phone_number   VARCHAR(20),
    hire_date      DATE,
    job_id         INT,
    salary         DECIMAL(10, 2),
    manager_id     INT,
    department_id  INT
);

INSERT INTO employees VALUES
(100, 'Steven',    'King',      'steven.king@x.org',     '515.123.4567', '1987-06-17',  4, 24000.00, NULL,  9),
(101, 'Neena',     'Kochhar',   'neena.kochhar@x.org',   '515.123.4568', '1989-09-21',  5, 17000.00,  100,  9),
(115, 'Alexander', 'Khoo',      'alexander.khoo@x.org',  '515.127.4562', '1995-05-18', 13,  3100.00,  114,  3),
(116, 'Shelli',    'Baida',     'shelli.baida@x.org',    '515.127.4563', '1997-12-24', 13,  2900.00,  114,  3),
(200, 'Jennifer',  'Whalen',    'jennifer.whalen@x.org', '515.123.4444', '1987-09-17',  3,  4400.00,  101,  1),
(201, 'Michael',   'Hartstein', 'michael.h@x.org',       '515.123.5555', '1996-02-17', 10, 13000.00,  100,  2),
(202, 'Pat',       'Fay',       'pat.fay@x.org',         '603.123.6666', '1997-08-17', 11,  6000.00,  201,  2),
(203, 'Susan',     'Mavris',    'susan.mavris@x.org',    '515.123.7777', '1994-06-07',  8,  6500.00,  101,  4);

Example 1 — pre-aggregated table in FROM

Build a department summary on the fly, then keep only the busy ones.

SQL

SELECT department_id, headcount, avg_salary
FROM (
    SELECT department_id,
           COUNT(*)         AS headcount,
           ROUND(AVG(salary),2) AS avg_salary
    FROM   employees
    GROUP  BY department_id
) AS dept_summary
WHERE headcount > 1
ORDER BY avg_salary DESC;

The inner query produces one row per department with two computed columns. The outer query treats dept_summary exactly like a table.

Example 2 — JOIN with an inline view

Join each employee to the average salary of their department, computed inline.

SQL

SELECT e.first_name,
       e.salary,
       d.avg_salary
FROM   employees e
JOIN (
    SELECT department_id,
           AVG(salary) AS avg_salary
    FROM   employees
    GROUP  BY department_id
) d ON d.department_id = e.department_id
ORDER  BY e.department_id, e.salary DESC;

Now every row shows the employee's salary alongside their department's average — and it works without any window functions.

Example 3 — Top-N per group

Get the highest-paid employee per department by joining to an inline view of department maxima.

SQL

SELECT e.department_id, e.first_name, e.salary
FROM   employees e
JOIN (
    SELECT department_id, MAX(salary) AS max_sal
    FROM   employees
    GROUP  BY department_id
) m ON m.department_id = e.department_id
   AND m.max_sal       = e.salary;

Inline view vs CREATE VIEW

Inline subquery (in FROM)	CREATE VIEW
Defined inside one query	Stored permanently in the database
Anonymous; alias is local	Named, reusable from anywhere
No privileges to manage	Privileges granted/revoked like a table
Best for one-off queries	Best for repeated queries / abstractions

Inline view vs CTE

A Common Table Expression (CTE) — WITH name AS (...) — is another way to name an intermediate result. It's often more readable than nesting inline views, especially when the same intermediate result is referenced more than once.

SQL — same query as Example 1, written as a CTE

WITH dept_summary AS (
    SELECT department_id,
           COUNT(*)             AS headcount,
           ROUND(AVG(salary),2) AS avg_salary
    FROM   employees
    GROUP  BY department_id
)
SELECT department_id, headcount, avg_salary
FROM   dept_summary
WHERE  headcount > 1
ORDER  BY avg_salary DESC;

💡 Pick the form that reads better. Inline views work great for a single intermediate set. CTEs shine when you want to reference the intermediate set more than once or build a multi-step pipeline that reads top-to-bottom.

Recap

An inline subquery sits in the FROM clause and behaves like a temporary table.
It must have an alias.
You can join, filter, group, or select from it like any other table.
Differs from CREATE VIEW — inline views aren't stored or shared.
For multi-step or reusable intermediates, prefer a CTE (WITH) for readability.