What Is a Derived Table?

A derived table is a temporary result set created inside a SQL query. It exists only for the life of that one query. You build it using a SELECT statement and give it a name so you can join to it just like a normal table.

If you calculate anything inside the derived table, such as a SUM() or MAX(), you need to assign an alias to those columns.

Why Use Derived Tables?

Derived tables are great when:

  • You want to pre-aggregate or filter data before joining it.
  • You want to keep your query logic clean and readable.
  • You need temporary logic without creating a physical table or view.

Inline Derived Table Example

Most derived tables are created right in the FROM clause. Here’s a great use case: we want to sum each customer’s orders and join that summary back to the raw order data.

Using a WITH Clause (CTE Style)

You can also create a derived table at the top of your query using a WITH clause. Just remember, you can’t run the WITH section by itself. It only works when the full query runs together.

Starting with a Three-Table Join

Before we add multiple derived tables, let’s look at a basic join across three real tables: CLAIMS, PROVIDERS, and SERVICES.

Adding Three Derived Tables

Now we’ll take that same query and join it with three derived tables:

  • Two are defined at the top using WITH
  • The third is created inline in the FROM clause

Note: When using multiple WITH tables, you only write the WITH keyword once. A comma separates each additional table.

Final Thoughts

Derived tables are one of your best friends in SQL. Whether you use the WITH clause or write them inline, they let you:

  • Pre-aggregate or filter data for cleaner joins
  • Make long queries easier to read
  • Avoid creating unnecessary temp tables or views

You’ve Just Mastered Derived Tables in Greenplum!

Now that you’ve seen how to build them, join them, and even combine multiple derived tables, you’re ready to take on more complex queries with confidence.

Posted in Blog