Derived Table

How derived tables work

Derived tables act as temporary views that exist only within the scope of the containing query. They're created using subqueries in the FROM clause and can be referenced like regular tables, enabling powerful data transformations and logical query organization.

-- ⚠️ ANSI (requires QuestDB adaptation)
SELECT dt.avg_price, dt.symbol
FROM (
    SELECT symbol, AVG(price) as avg_price
    FROM trades
    GROUP BY symbol
) dt
WHERE dt.avg_price > 100;

This structure allows you to:

1. Break down complex queries into more manageable pieces
2. Create intermediate results that can be reused within the query
3. Apply transformations to data before joining or further processing

Benefits and use cases

Derived tables are particularly valuable in time-series analysis and financial data processing:

Data transformation

• Computing period-over-period changes
• Creating aggregated snapshots at specific time intervals
• Preparing data for complex joins

Query optimization

• Reducing redundant calculations
• Enabling more efficient join strategies
• Supporting partition pruning optimizations

Comparison with other SQL constructs

Derived tables vs CTEs

While similar to common table expressions, derived tables:

• Are scoped only to the specific query
• Cannot be referenced multiple times
• May be materialized differently by the query optimizer

Derived tables vs Views

Unlike permanent database views, derived tables:

• Exist only during query execution
• Don't require storage management
• Can be optimized based on the specific query context

Performance considerations

When working with derived tables:

1. Materialization: The database may materialize (temporarily store) the derived table results
2. Query planning: The query planner can optimize derived table execution based on usage
3. Memory usage: Large derived tables may impact query performance due to memory requirements

Optimization tips

• Use derived tables to precompute complex calculations once
• Consider replacing deeply nested derived tables with CTEs for better readability
• Let the query optimizer handle materialization decisions unless specific hints are needed

-- ✅ QuestDB-valid
WITH trades_sample AS (
    SELECT symbol, price, timestamp 
    FROM trades 
    SAMPLE BY 1m
)
SELECT * FROM trades_sample
WHERE price > 100
LIMIT 10;

The key to effective derived table usage is understanding their role in query organization and letting the database engine optimize their execution based on the specific use case and data patterns.