Zwischentabelle

Zwischentabelle

A Zwischentabelle, or intermediate table, is a temporary data structure used in relational databases to hold intermediate results during query processing, data transformations, or ETL tasks. It serves as a staging area between input data and final output, enabling complex operations to be broken into smaller steps and potentially improving performance by avoiding repeated computations or expensive re-joins.

Implementation and scope vary by database system. In many DBMS, a Zwischentabelle is created as a temporary table, either local (session-scoped) or global (visible to multiple sessions but with per-session data). Common dialects include: SQL Server, where local temporary tables are created with a leading #; PostgreSQL and MySQL, which support CREATE TEMPORARY TABLE; and Oracle, which uses Global Temporary Tables whose data persists for the duration of a session. Typically, a Zwischentabelle exists for the lifetime of a connection or transaction and is dropped automatically or explicitly when no longer needed.

Typical use cases include storing results of subqueries or aggregates for subsequent joins, breaking up multi-step transformations into discrete operations, staging data during ETL processes, and supporting iterative computations inside stored procedures or scripts.

Lifecycle and management involve creating the table, optionally indexing it to speed up repeated access, and dropping it after use. Considerations include resource usage and cleanup, portability across DBMS implementations, and potential locking or contention in concurrent environments. Zwischentabellen are distinct from derived tables or common table expressions in that they involve actual temporary storage, whereas derived tables and CTEs are often computed on the fly.