SQL - Temporary Tables

SQL - Temporary Tables

Temporary tables are special tables that exist only for the duration of a database session or transaction. They are incredibly useful for storing intermediate results, simplifying complex queries, and isolating data manipulation without affecting permanent tables.

1. What are Temporary Tables and Why Use Them?

A temporary table, or ‘temp table’, is a table created in the database’s temporary storage area (like tempdb in SQL Server). You can perform standard SQL operations like SELECT, INSERT, UPDATE, DELETE, and JOIN on them, just like regular tables.

Common use cases include:

• Storing Intermediate Results: In a multi-step process, you can store the result of the first step in a temp table and then query it in subsequent steps. This can improve readability and sometimes performance over a single, massive query.
• Data Staging: Holding data fetched from an external source or from a complex join before it’s cleaned, transformed, and inserted into a final permanent table.
• Isolating Operations: Performing complex calculations or updates on a subset of data in a temp table before applying the final results to a permanent table.

2. Creating and Dropping Temporary Tables (Cross-Platform)

The syntax for creating temporary tables is similar across most RDBMS, but with slight variations.

MySQL and PostgreSQL

Both use the CREATE TEMPORARY TABLE syntax. The table is automatically dropped when the session ends.

-- Create a temporary table
CREATE TEMPORARY TABLE Temp_ActiveUsers (
    user_id INT PRIMARY KEY,
    last_login_date DATE
);

-- Manually drop the temporary table (optional, as it's auto-dropped on session end)
DROP TEMPORARY TABLE Temp_ActiveUsers;

PostgreSQL offers an ON COMMIT clause (CREATE TEMP TABLE ... ON COMMIT [PRESERVE ROWS | DELETE ROWS | DROP]) that gives you fine-grained control over the table’s behavior within a transaction.

SQL Server (T-SQL)

SQL Server uses a # prefix for local temporary tables and ## for global ones.

-- Create a local temporary table (visible only to the current session)
CREATE TABLE #Temp_ProductSales (
    product_id INT,
    total_sales DECIMAL(12, 2)
);

-- Create a global temporary table (visible to all sessions)
-- Use with caution due to potential for naming conflicts.
CREATE TABLE ##Shared_Config (
    config_key VARCHAR(50),
    config_value VARCHAR(255)
);

-- Manually drop the table
DROP TABLE #Temp_ProductSales;

3. Scope and Lifetime of Temporary Tables

• Session-Scoped (MySQL, PostgreSQL, SQL Server Local #): The table is created and is only visible within the database session that created it. Other sessions cannot see or access it. It is automatically destroyed when the session is closed.
• Global-Scoped (SQL Server Global ##): The table is visible to all active database sessions. It is destroyed automatically when the session that created it closes AND no other session is actively using it.

4. Practical Use Case: Staging Data for a Report

Imagine you need to generate a report of top-spending customers for the last month. The calculation involves joining orders and order items. A temp table can simplify this.

Step 1: Create a temp table to hold monthly spending per customer.

CREATE TEMPORARY TABLE CustomerSpending (
    customer_id INT PRIMARY KEY,
    total_spent DECIMAL(10, 2) NOT NULL
);

Step 2: Populate the temp table with aggregated data.

INSERT INTO CustomerSpending (customer_id, total_spent)
SELECT
    o.customer_id,
    SUM(oi.quantity * oi.unit_price)
FROM Orders o
JOIN OrderItems oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2023-10-01' AND o.order_date < '2023-11-01'
GROUP BY o.customer_id;

Step 3: Generate the final report by joining with the Customers table.

SELECT
    c.name,
    c.email,
    cs.total_spent
FROM Customers c
JOIN CustomerSpending cs ON c.customer_id = cs.customer_id
ORDER BY cs.total_spent DESC
LIMIT 10;

5. Modern Alternatives: CTEs and Table Variables

Common Table Expressions (CTEs)

For simplifying a single, complex query, a CTE (using a WITH clause) is often a better choice. A CTE exists only for the duration of a single statement. It improves readability without the overhead of creating a physical table in temp storage.

WITH CustomerSpending AS (
    SELECT
        o.customer_id,
        SUM(oi.quantity * oi.unit_price) AS total_spent
    FROM Orders o
    JOIN OrderItems oi ON o.order_id = oi.order_id
    WHERE o.order_date >= '2023-10-01' AND o.order_date < '2023-11-01'
    GROUP BY o.customer_id
)
SELECT
    c.name,
    c.email,
    cs.total_spent
FROM Customers c
JOIN CustomerSpending cs ON c.customer_id = cs.customer_id
ORDER BY cs.total_spent DESC
LIMIT 10;

Table Variables (SQL Server)

SQL Server also offers table variables (e.g., DECLARE @MyTable TABLE (...)). They are similar to local temp tables but are scoped to the current batch or stored procedure and generally involve less logging, making them lighter for very small datasets. However, they lack statistics, which can lead to poor performance with larger amounts of data.