Use A Subquery As A Derived Table In FROM

Wrap a `SELECT` in the `FROM` clause to create a temporary result set you can filter or join against as if it were a real table.

Created Apr 26, 2026 Last updated Apr 26, 2026 5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Assign letter grades in a subquery, then filter to only passing grades

The inner query assigns a grade to every student using CASE. The outer query then filters on grade = 'A' — which would not be possible in a WHERE clause at the same query level as the CASE expression. Only Bob (92) and Dave (95) meet the threshold.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE scores (student VARCHAR(50), score INT);

INSERT INTO
  scores
VALUES
  ('Alice', 85),
  ('Bob', 92),
  ('Carol', 78),
  ('Dave', 95);

Validated query Shared across supported engines.

SQL

SELECT
  student,
  score,
  grade
FROM
  (
    SELECT
      student,
      score,
      CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        ELSE 'C'
      END AS grade
    FROM
      scores
  ) AS graded
WHERE
  grade = 'A'
ORDER BY
  student;

Expected result Returned rows for the shared example.

student	score	grade
Bob	92	A
Dave	95	A

Identical syntax and result across all engines.

Useful when

Where this command helps.

filtering on a computed alias that cannot be referenced in the same SELECT's WHERE clause
pre-aggregating a table and then joining or filtering the summary

Explanation

What the command is doing.

A subquery in the FROM clause is called a derived table or inline view. It is evaluated first and its result is handed to the outer query as a named table. This pattern is useful when you need to filter on a column alias or an aggregated value that cannot appear in the WHERE clause of the same query level. The subquery must be given an alias; in PostgreSQL and MySQL the alias is mandatory. For readable code, a WITH CTE is often preferred when the derived table is complex.

Use A Subquery As A Derived Table In FROM

Assign letter grades in a subquery, then filter to only passing grades

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.