What is a Derived Table in SQL?

If you’ve been writing SQL for a while, you’ve probably run into situations where you need to query data that’s already been aggregated, filtered, or transformed in some way. Maybe you need to calculate an average first, then find all the rows above that average. Or perhaps you need to group data by category, then filter those grouped results.

This is where derived tables can come in handy. Derived tables let you build queries in layers, where one query’s output becomes another query’s input, all within a single SQL statement.

How it Works

A derived table is a subquery that’s placed in the FROM clause of a SQL statement. It’s kind of like a temporary table within your main SQL query.

To create a derived table, you write a SELECT statement, wrap it in parentheses, and stick it in your FROM clause. Now you can treat those results as if they were an actual table.

The main thing to understand is that derived tables only exist for the duration of your query. Once the query finishes executing, the derived table disappears. It’s not stored anywhere in your database, which makes it different from actual tables, views, or even temporary tables.

The Basic Syntax

Here’s what a derived table looks like in its simplest form:

SELECT column1, column2
FROM (
    SELECT column1, column2, column3
    FROM actual_table
    WHERE some_condition
) AS derived_table_name
WHERE another_condition;

Notice that you must give your derived table an alias (the AS derived_table_name part). This is required in most database systems, mainly because SQL needs a way to reference the derived table in the outer query.

Example

Let’s look at a scenario where we’re analyzing customer orders and want to find customers whose total spending exceeds a certain threshold.

First, we’ll create and populate an orders table:

-- Create sample data
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    order_date DATE,
    amount DECIMAL(10,2)
);

INSERT INTO orders VALUES
(1, 'Alex Rivera', '2024-01-15', 145.00),
(2, 'Jordan Blake', '2024-01-18', 89.50),
(3, 'Alex Rivera', '2024-02-03', 210.00),
(4, 'Casey Morgan', '2024-02-10', 67.25),
(5, 'Jordan Blake', '2024-02-14', 122.75),
(6, 'Alex Rivera', '2024-03-01', 198.50),
(7, 'Casey Morgan', '2024-03-05', 45.00);

Now we’ll query that data using a derived table to get the result we want:

SELECT customer_name, total_spent, order_count
FROM (
    SELECT 
        customer_name,
        SUM(amount) AS total_spent,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_name
) AS customer_totals
WHERE total_spent > 200
ORDER BY total_spent DESC;

Output:

customer_name  total_spent  order_count
-------------  -----------  -----------
Alex Rivera    553.5        3          
Jordan Blake   212.25       2          

The derived table here aggregates all orders by customer, calculating their total spending and order count. The outer query then filters to show only high-value customers who’ve spent more than $200. This gives us a clean, readable way to perform the aggregation and filtering in logical steps.

Why Use Derived Tables?

Derived tables are useful when you need to perform multi-step operations that would be awkward or impossible to do in a single query level. They’re particularly handy in a few scenarios:

• Breaking down complex logic: Sometimes you need to aggregate data first, then filter or join based on those aggregates. Derived tables let you perform the aggregation in the inner query and use the results in the outer query.
• Working with grouped data: When you’re using the GROUP BY clause and need to do additional filtering or calculations on the grouped results, derived tables provide a clean way to handle this without creating views or temporary tables.
• Avoiding repeated subqueries: If you’d otherwise need to write the same subquery multiple times in different parts of your SELECT statement, a derived table lets you write it once and reference it multiple times.

Derived Tables vs Other Options

You might be wondering why you’d use a derived table instead of a regular view or a common table expression (CTE). Views are great when you need to reuse the same query logic across multiple different queries or scripts. They’re persistent database objects that stick around. Derived tables, on the other hand, are ideal for one-off operations within a single query.

CTEs (the WITH clause) are actually very similar to derived tables in terms of functionality. Many developers prefer CTEs because they consider them more readable (the query definition comes before the main query rather than being nested inside it). However, derived tables are universally supported across different SQL databases, while CTE syntax might vary slightly.

Things to Keep in Mind

There are a few considerations worth noting when working with derived tables.

Performance can sometimes be a concern with derived tables, especially if the inner query returns a large result set. The database has to materialize those results before the outer query can work with them. In some cases, rewriting your query or using indexes might give you better performance.

You also can’t directly reference columns from the outer query inside a derived table. Each derived table is evaluated independently, so if you need to correlate data between the inner and outer queries, you might need a correlated subquery instead (which works differently than a derived table).

Another thing to remember is that column aliases defined in the derived table’s SELECT list are what you use to reference those columns in the outer query. You can’t reference the original table’s column names unless they’re explicitly selected and aliased in the derived table.

Summary

Derived tables are a handy tool in SQL that let you build more sophisticated queries by treating subquery results as temporary tables (without actually creating temporary tables). They’re great for breaking complex operations into manageable steps, working with aggregated data, and organizing your query logic in a way that makes sense. While they’re not always the best solution for every problem, they’re definitely worth having in your SQL toolkit.