How do you create and use a temporary table to store intermediate results in a query?
EveClark
Posted by EveClark
Last Updated: July 21, 2024
temporary table
Creating and using a temporary table to store intermediate results in a SQL query can enhance performance and simplify complex queries. Here’s a step-by-step guide on how to create and use a temporary table:
Step 1: Create the Temporary Table
You can create a temporary table using the CREATE TEMPORARY TABLE statement. The structure of the temporary table should match the data you intend to store. 
CREATE TEMPORARY TABLE temp_table_name (
    column1 datatype,
    column2 datatype,
    ...
);
Example:
CREATE TEMPORARY TABLE temp_sales (
    order_id INT,
    product_id INT,
    quantity INT,
    total_amount DECIMAL(10, 2)
);
Step 2: Insert Data into the Temporary Table
You can insert data into the temporary table using the INSERT INTO statement, often as a result of a query. 
INSERT INTO temp_table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;
Example:
INSERT INTO temp_sales (order_id, product_id, quantity, total_amount)
SELECT id, product_id, quantity, price * quantity
FROM orders
WHERE order_date >= '2023-01-01';
Step 3: Use the Temporary Table in Queries
Once you have stored intermediate results in the temporary table, you can use it in subsequent queries just like any other table. 
SELECT *
FROM temp_table_name
WHERE condition;
Example:
SELECT product_id, SUM(quantity) AS total_quantity, SUM(total_amount) AS total_sales
FROM temp_sales
GROUP BY product_id;
Step 4: Cleaning Up
Temporary tables are automatically dropped at the end of the session or when the transaction that created them is completed. However, if you want to manually drop the temporary table, you can do so with: 
DROP TABLE temp_table_name;
Example:
DROP TABLE temp_sales;
Considerations
- Temporary tables are session-specific: They are visible only to the session that created them and are dropped automatically at the end of the session. - Performance: Using a temporary table can improve performance for complex queries that require multiple steps and can significantly reduce the total execution time. - Indexing: You can create indexes on temporary tables to improve query performance further, just like you would with regular tables.
Complete Example
Here’s a complete flow: 
-- Step 1: Create a temporary table
CREATE TEMPORARY TABLE temp_sales (
    order_id INT,
    product_id INT,
    quantity INT,
    total_amount DECIMAL(10, 2)
);

-- Step 2: Insert data into the temporary table
INSERT INTO temp_sales (order_id, product_id, quantity, total_amount)
SELECT id, product_id, quantity, price * quantity
FROM orders
WHERE order_date >= '2023-01-01';

-- Step 3: Use the temporary table to get aggregated results
SELECT product_id, SUM(quantity) AS total_quantity, SUM(total_amount) AS total_sales
FROM temp_sales
GROUP BY product_id;

-- Step 4: Optionally drop the temporary table
DROP TABLE temp_sales; -- This step is optional; it will be automatically dropped at the end of the session
By following these steps, you can effectively use temporary tables in your SQL queries to manage and analyze data efficiently.
Default Image