How do you create a temporary table in SQL?

Creating a temporary table in SQL allows you to store intermediate results for the duration of a session or transaction, depending on the database system. The basic syntax involves using the CREATE TEMPORARY TABLE statement (or similar, depending on the SQL dialect), followed by column definitions. For example, in PostgreSQL or MySQL, you might write CREATE TEMPORARY TABLE temp_users (id INT, name VARCHAR(50));. Temporary tables are session-specific, meaning they’re only visible to the current database connection and are automatically dropped when the session ends. Some systems, like SQL Server, use a slightly different syntax with a # prefix (e.g., CREATE TABLE #temp_users), but the core concept remains the same: these tables exist temporarily to simplify complex queries or staging data.

Temporary tables are particularly useful for breaking down complex operations into manageable steps. For instance, if you need to join large datasets or perform multiple transformations, storing intermediate results in a temporary table can improve readability and performance. Suppose you’re analyzing sales data: you could create a temporary table to store filtered results (e.g., sales from the last month) and then run aggregations or joins against it. Here’s an example in standard SQL:

CREATE TEMPORARY TABLE recent_orders AS
SELECT order_id, customer_id, total 
FROM orders 
WHERE order_date >= '2023-10-01';

This creates a temporary table containing recent orders, which you can query later without reprocessing the WHERE clause. Temporary tables also reduce contention with other sessions, as their data isn’t visible outside the current connection.

When using temporary tables, consider their scope and lifespan. In some databases like PostgreSQL, temporary tables are transaction-scoped if created within a transaction block, while others like MySQL drop them at the end of the session. Be mindful of naming conflicts—most systems prevent clashes with permanent tables, but reusing the same temporary table name in nested procedures could cause issues. Additionally, while temporary tables can be indexed for faster queries, overusing them for small datasets might introduce unnecessary overhead. Always clean up explicitly when done (e.g., DROP TEMPORARY TABLE recent_orders;) to free resources, even though automatic deletion occurs eventually. This ensures predictable performance in environments with frequent connections.