Temporary Tables
Temporary tables are session-scoped tables for staging, transformation, and repeat-query workflows.
Temporary Table Characteristics
| Characteristic | Behavior |
|---|---|
| Scope | Visible only in current session |
| Lifetime | Dropped automatically at session end |
| Use case | Multi-step data processing |
Core Workflow
CREATE TEMPORARY TABLE tmp_paid_orders AS
SELECT order_id, customer_id, total_amount
FROM orders
WHERE status = 'paid';
SELECT customer_id, SUM(total_amount) AS total_spend
FROM tmp_paid_orders
GROUP BY customer_id;
DROP TEMPORARY TABLE IF EXISTS tmp_paid_orders;
When to Choose Temporary Tables
| Scenario | CTE | Temporary table |
|---|---|---|
| One-pass transformation | Better | Acceptable |
| Multi-step reuse in session | Limited | Better |
| Debugging intermediate states | Limited | Better |
Common Pitfalls
| Pitfall | Consequence | Prevention |
|---|---|---|
| Forgetting cleanup in long sessions | Session memory growth | Drop temp tables explicitly when done |
| Naming collisions in scripts | Script failures | Use prefixed names like tmp_report_* |
| Assuming visibility across sessions | Unexpected missing table errors | Recreate per connection/session |
Quick Reference
CREATE TEMPORARY TABLE tmp_x AS SELECT ...;
INSERT INTO tmp_x SELECT ...;
SELECT * FROM tmp_x;
DROP TEMPORARY TABLE IF EXISTS tmp_x;