Temporary Tables and Intermediate Tables in SQL

October 16, 2025

Temporary Tables and Intermediate Tables in SQL

1. AIM

To study and implement Temporary Tables, Common Table Expressions (CTEs), and Subqueries in SQL for storing intermediate results and performing complex data processing.

2. THEORY

2.1 Temporary Tables

A temporary table is a table that exists only for the duration of a database session.

Created with: CREATE TEMPORARY TABLE
Automatically dropped when session ends
Useful for storing intermediate results
Can be explicitly dropped with: DROP TEMPORARY TABLE

2.2 Intermediate Tables

Intermediate tables are used to simplify complex operations. They can be created using:

(a) CTE (Common Table Expression)

A CTE is a temporary result set defined inside a query using the WITH clause.

(b) Temporary Tables

Physically created in temp storage, used inside the session.

(c) Subqueries

Nested queries used in SELECT, FROM, or WHERE clauses.

3. SQL COMMANDS & SYNTAX

3.1 Syntax: CREATE TEMPORARY TABLE


CREATE TEMPORARY TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

3.2 Syntax: CTE


WITH CTE_Name AS (
    SELECT ...
)
SELECT * FROM CTE_Name;

3.3 Subquery Syntax


SELECT col1
FROM table
WHERE col2 > (SELECT ...);

4. EXAMPLES

Experiment 1: Temporary Table

Step 1: Create a Temporary Table


CREATE TEMPORARY TABLE temp_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);

Step 2: Insert Records


INSERT INTO temp_table (name)
VALUES ('John'), ('Alice'), ('Bob');

Step 3: Select Records


SELECT * FROM temp_table WHERE name = 'Alice';

Expected Output

id	name
2	Alice

Step 4: Drop Temporary Table (Optional)


DROP TEMPORARY TABLE temp_table;

Experiment 2: Using CTE (WITH Clause)

Objective:

Find departments where the average salary is above 50,000.

CTE Query


WITH AvgSalaryByDept AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT department_id
FROM AvgSalaryByDept
WHERE avg_salary > 50000;

Explanation

CTE first calculates average salary by department
Outer query filters departments with high average salary

Experiment 3: Temporary Table for Intermediate Result

Objective:

Store employees hired in last 1 year and compute their average tenure.

Step 1: Create Temporary Table


CREATE TEMPORARY TABLE recent_hires AS (
    SELECT employee_id, hire_date
    FROM employees
    WHERE hire_date >= CURDATE() - INTERVAL 1 YEAR
);

Step 2: Query on Temporary Table


SELECT AVG(DATEDIFF(CURDATE(), hire_date)) AS avg_tenure_in_days
FROM recent_hires;

Step 3: Drop Table


DROP TEMPORARY TABLE recent_hires;

Experiment 4: Subquery as Intermediate Table

Objective:

Find employees whose salary is above their department average.

Query


SELECT employee_id, salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

Explanation

Subquery generates average salary per department
Main query compares each employee’s salary with department average

5. RESULT

Temporary tables, CTEs, and subqueries were successfully implemented.
We observed that:

Temporary tables store session-level intermediate data
CTEs simplify complex queries and improve readability
Subqueries allow nested logical filtering and computations

Thus, temporary and intermediate tables help in modularizing and optimizing SQL queries.