Temporary tables in SQL

A scratch table that disappears with the connection, which is exactly what a report needs.

CREATE TEMP TABLE recent_orders AS
SELECT
    id,
    customer_id,
    total
FROM orders
WHERE placed_at >= DATE('now', '-30 days');

CREATE INDEX temp.recent_by_customer
    ON recent_orders (customer_id);

SELECT COUNT(*) AS recent
FROM recent_orders;

How it works

1. TEMP tables live in a private database and vanish on disconnect.
2. CREATE TABLE AS SELECT takes its columns from the query.
3. Two connections can each have a temp table of the same name.

Keywords and builtins used here

• AS
• COUNT
• CREATE
• DATE
• FROM
• INDEX
• ON
• SELECT
• TABLE
• WHERE

The run, in numbers

Lines

13

Characters to type

237

Tokens

45

Three-star pace

90 tpm

At the three-star pace of 90 tokens a minute, this run takes about 30 seconds.

Type this snippet

Step 3 of 3 in Views & scratch tables, step 12 of 16 in Schema & constraints.

← Previous Next →