What are temporary tables?

Temporary tables in MySQL are special tables that exist temporarily during a database session. They are useful for storing intermediate results, performing complex queries, or breaking down large tasks into smaller steps without affecting the main database tables.

Here’s a detailed breakdown:

---

Key Features

1. Session-specific:
   • Temporary tables are visible only to the session (connection) that created them.
   • When the session ends, the table is automatically dropped.
2. Same syntax as normal tables:
   • You can define columns, data types, and even indexes.
3. No name conflicts:
   • Different sessions can create temporary tables with the same name without conflict.
4. Automatic cleanup:
   • If the session ends unexpectedly, MySQL automatically removes the temporary table.

---

Creating a Temporary Table

CREATE TEMPORARY TABLE temp_employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10,2)
);

---

Using a Temporary Table

-- Insert data
INSERT INTO temp_employees (id, name, salary) 
VALUES (1, 'John', 50000), (2, 'Alice', 60000);

-- Query data
SELECT * FROM temp_employees WHERE salary > 50000;

---

Dropping a Temporary Table

DROP TEMPORARY TABLE IF EXISTS temp_employees;

If you don’t drop it manually, it will be automatically removed when your session ends.

---

Advantages

• Helps simplify complex queries.
• Reduces repeated computations by storing intermediate results.
• Safe to use in multi-user environments because each session has its own version.

---

Limitations

• Cannot be referenced by other sessions.
• Cannot have foreign keys referencing permanent tables (or vice versa).
• Data is lost when the session ends.

Here’s a clear comparison between Temporary Tables and Regular Tables in MySQL:

Feature	Temporary Table	Regular Table
Visibility	Only visible within the session that created it	Visible to all sessions/users with access
Lifetime	Exists only for the duration of the session or until manually dropped	Permanent until explicitly dropped
Creation Syntax	CREATE TEMPORARY TABLE ...	CREATE TABLE ...
Name Conflicts	Different sessions can have tables with the same name without conflict	Table names must be unique within a database
Foreign Key Constraints	Cannot reliably have foreign keys to/from permanent tables	Can have foreign keys between tables
Use Case	Storing intermediate or temporary data for queries	Storing persistent data for applications
Automatic Cleanup	Automatically deleted when session ends	Must be explicitly deleted
Performance	Useful for speeding up complex queries or intermediate results	Standard storage and retrieval operations