SQL Temporary Tables

A temporary table behaves like any other table — it stores rows, you can join it, filter it, even index it — except that it belongs to your connection and vanishes when that connection closes. Nobody else sees it, and you never have to remember to clean it up.

create temp table active_customers as
select customer_id, first_name, last_name, state
from customers
where is_active

TEMP and TEMPORARY are the same keyword. From there it is an ordinary table:

select a.state,
       count(*) as orders
from active_customers a
join orders o on o.customer_id = a.customer_id
group by a.state
order by orders desc, a.state

How long "temporary" actually lasts

The lifetime is the connection, not the day and not the browser tab. Open a second connection to the same database and it cannot see your temp table at all — the name simply does not resolve.

That is not a quirk of this site. Any tool that opens a fresh connection per statement — a lot of BI tools, notebook cells with their own connections, pooled application connections — behaves the same way. Temp tables are for work that happens inside one script, start to finish.

Temp tables and temp views

Both disappear with the connection. The difference is the same as the permanent versions: a temp table stores rows, atemp view stores a query and re-runs it.

-- computed once, held as rows
create temp table customer_revenue as
select o.customer_id,
       sum(oi.quantity * oi.unit_price) as revenue
from orders o
join order_items oi on oi.order_id = o.order_id
group by o.customer_id;

-- re-runs on every reference
create temp view active_customer_names as
select customer_id, first_name
from customers
where is_active;

Reach for the temp table when the step is expensive and you read it several times. Reach for the temp view when the step is cheap and you mainly wanted to give it a name.

Rebuilding one mid-script

create or replace temp table active_customers as
select customer_id, first_name
from customers
where is_active
  and state = 'PA'

CREATE OR REPLACE TEMP TABLE swaps the definition and the rows.CREATE TEMP TABLE IF NOT EXISTS does the opposite — it keeps whatever is already there and tells you nothing, which is the same trap it is on permanent tables.

The name collision to know about

A temp table can have the same name as a permanent one. When that happens the temp table wins every unqualified lookup, and schema-qualifying does not break the tie: temp objects live in a database called temp whose schema is also called main, somain.customers still finds the temp one.

-- the temp one
select * from customers;

-- still the temp one
select * from main.customers;

-- the real, permanent table
select * from memory.main.customers;

Finding them

select database_name, table_name, temporary
from duckdb_tables()
where temporary
order by table_name

When a temp table is the right answer

• An expensive step read several times. ACTE only lives for one statement, so five statements means computing it five times.
• Debugging a long query. Break it into steps, land each in a temp table, and count rows between them to find where the number goes wrong.
• Staging a load before you decide the rows are good enough to write somewhere permanent.
• Scratch work on a shared database where you should not be creating real tables at all.

And when not to: anything another person or another session needs to read. They cannot see it. That wants a real table or a view.