Temporary Tables and the WITH Clause in SQL

1. What Is a Temporary Table?

A temporary table is a database table that:

• Exists only during a session
• Stores intermediate results
• Is automatically removed when the session ends

Temporary tables are useful for short-term analytical tasks.

They function like “working memory” for SQL queries.

2. Why Use Temporary Tables?

Temporary tables help when:

• Working with large datasets
• Reusing filtered subsets repeatedly
• Breaking complex queries into logical stages
• Simplifying multi-step joins
• Improving readability and efficiency

Instead of repeatedly filtering or joining, you store the result once and reuse it.

3. Example Use Cases

3.1 Complex Multi-Table Joins

Instead of:

• Joining 7–8 tables in one query

You can:

1. Join smaller tables first.
2. Store result in a temporary table.
3. Join that temp table to larger tables.

This reduces query complexity.

3.2 Repeated Subset Analysis

If analyzing:

• Only trips ≥ 60 minutes

Instead of filtering every time:

WHERE trip_duration >= 60

WHERE trip_duration >= 60

You create a temp table once and reuse it.

4. Creating a Temporary Table Using WITH (CTE)

In BigQuery and many SQL systems, the WITH clause creates a Common Table Expression (CTE).

A CTE:

• Acts like a temporary table
• Exists only within the query
• Improves readability

5. Example: Bike Trips Over 60 Minutes

Step 1: Define Temporary Table

WITH trips_over_1_hr AS (
    SELECT *
    FROM `bigquery-public-data.new_york.citibike_trips`
    WHERE tripduration >= 60
)

WITH trips_over_1_hr AS (
    SELECT *
    FROM `bigquery-public-data.new_york.citibike_trips`
    WHERE tripduration >= 60
)

Explanation:

• WITH → Start temporary definition.
• trips_over_1_hr → Name of temp table.
• AS → Assign query.
• Subquery inside parentheses defines the subset.

This does not permanently create a table.
It exists only for the query session.

6. Using the Temporary Table

After defining it, you can reference it like a regular table.

Example: Count Long Trips

SELECT COUNT(*) AS cnt
FROM trips_over_1_hr;

SELECT COUNT(*) AS cnt
FROM trips_over_1_hr;

This returns:

• Total number of trips ≥ 60 minutes.

7. Commenting in SQL

To add descriptive comments:

-- Count trips longer than 60 minutes

-- Count trips longer than 60 minutes

Or in some systems:

## Count trips longer than 60 minutes

## Count trips longer than 60 minutes

Comments improve collaboration and documentation.

8. Benefits of Using WITH (CTE)

8.1 Readability

Instead of deeply nested subqueries:

SELECT ...
FROM (
    SELECT ...
    FROM ...
)

SELECT ...
FROM (
    SELECT ...
    FROM ...
)

You define logical blocks at the top.

8.2 Reusability

Within the same query:

WITH subset AS (...)
SELECT ...
FROM subset;

WITH subset AS (...)
SELECT ...
FROM subset;

You can reference subset multiple times.

8.3 Efficiency

• Avoid repeated filtering
• Avoid rewriting complex conditions
• Reduce duplication

9. Temporary Tables vs Permanent Tables

Temporary tables are ideal for exploratory analysis.

10. Multi-Step Example Structure

WITH filtered_data AS (
    SELECT *
    FROM source_table
    WHERE condition
),
aggregated_data AS (
    SELECT column, COUNT(*)
    FROM filtered_data
    GROUP BY column
)
SELECT *
FROM aggregated_data;

WITH filtered_data AS (
    SELECT *
    FROM source_table
    WHERE condition
),
aggregated_data AS (
    SELECT column, COUNT(*)
    FROM filtered_data
    GROUP BY column
)
SELECT *
FROM aggregated_data;

This layered approach:

• Breaks analysis into logical phases.
• Improves clarity.
• Makes debugging easier.

11. When to Use Temporary Tables

Use temp tables when:

• You need repeated access to a filtered dataset.
• You are performing multiple calculations on the same subset.
• You want to simplify nested logic.
• You are testing intermediate results.
• You are building a report pipeline.

12. Analytical Advantages

Temporary tables:

• Improve organization
• Reduce repetitive code
• Simplify debugging
• Enhance team collaboration
• Support modular query design

They act as structured building blocks for complex analysis.

13. Summary

Temporary tables (via WITH clause):

• Store intermediate query results.
• Exist only during query execution.
• Simplify complex SQL logic.
• Allow repeated reuse of filtered subsets.
• Improve readability and efficiency.

They are powerful tools for managing multi-step SQL analysis and are foundational for scalable database workflows.