✏️ Explanatory Question
Level: Advanced — Temporary tables are invaluable for breaking down complex, multi-step operations.
A temporary table is a special table that exists only for the duration of the current session (connection) and is automatically dropped when the session ends. It's created with CREATE TEMPORARY TABLE and is visible only to the session that created it.
DROP TEMPORARY TABLE.
| Feature | Temporary Table | Regular Table |
|---|---|---|
| Lifespan | Current session only | Permanent |
| Visibility | Only creating session | All sessions |
| Auto-dropped? | Yes (on disconnect) | No |
| Name conflicts | None across sessions | Must be unique |
| Stored in | Memory/disk (temp) | Permanent storage |
SHOW TABLES.-- Create a temporary table from a query
CREATE TEMPORARY TABLE temp_high_earners AS
SELECT emp_id, name, salary
FROM employees
WHERE salary > 60000;
-- Use it like a normal table within the session
SELECT COUNT(*) FROM temp_high_earners;
SELECT t.name, d.dept_name
FROM temp_high_earners t
JOIN departments d ON t.emp_id = d.dept_id;
-- Create an empty temp table with a defined structure
CREATE TEMPORARY TABLE temp_summary (
dept_id INT,
total INT
);
INSERT INTO temp_summary
SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id;
-- Manually drop it (or it auto-drops on disconnect)
DROP TEMPORARY TABLE temp_high_earners;