SQL一時テーブルの作成｜TEMPORARY TABLEとCTEの違い

一時テーブルとは、作成した接続（セッション）の中だけで使え、接続を閉じると自動で消える作業用のテーブルです。 MySQL と PostgreSQL では CREATE TEMPORARY TABLE、SQL Server では名前の先頭に # を付けた CREATE TABLE #名前 で作ります。途中の集計結果をいったん置いておき、何度も参照したりインデックスを張ったりできるのが、CTE（WITH 句）やサブクエリとの大きな違いです。

この記事では、MySQL／PostgreSQL／SQL Server／Oracle の作成構文と寿命を DB 別の表 にまとめ、一時テーブル・CTE・サブクエリ・ビューを「再利用できるか・インデックスを張れるか・いつ消えるか」で比べる早見表 を用意しました。さらに、MySQL で同じ一時テーブルを1つのクエリの中で2回参照するとエラーになる例や、別の接続からは見えないことを、MySQL 8.0.46 と PostgreSQL 16.15 で実際に動かした出力つきで紹介します。

一時テーブル・CTE・サブクエリ・ビューの早見表

迷ったときの目安は次の3行です。

• 1つの SQL 文の中で完結するなら CTE（読みやすく、後片付けも不要）。
• 複数の SQL 文で同じ中間結果を使う、またはインデックスがほしいなら一時テーブル。
• ほかの人や別の接続からも同じ定義を使いたいならビュー。

DB別の一時テーブル作成構文と寿命の一覧

• MySQL と PostgreSQL の行は今回実行して確かめた内容です。
• SQL Server の #・## の見える範囲と消えるタイミングは、Microsoft Learn の「テーブル」のページを開いて確認しました（一時テーブルは tempdb に格納されます）。SQL Server のコード自体は手元に実行環境がないため 未実行 です。
• Oracle は実行環境がなく、公式ページも今回は開けていないため 未実行・一般に知られた仕様 として載せています。プライベート一時表は Oracle Database 18c 以降の機能で、名前の先頭に ORA$PTT_ が必要とされています。

MySQLで一時テーブルを作成する（CREATE TEMPORARY TABLE）

以降の例では、次の注文テーブルを使います（MySQL・PostgreSQL 共通）。

CREATE TABLE orders (
  order_id   INT PRIMARY KEY,
  member_id  INT,
  amount     INT,
  ordered_on DATE
);
INSERT INTO orders VALUES
  (1,101,3000,'2026-09-01'),(2,102,1200,'2026-09-03'),(3,101,4500,'2026-09-10'),
  (4,103,800,'2026-09-15'),(5,102,2600,'2026-09-20'),(6,101,1500,'2026-09-28');

SELECTの結果から作る

一番よく使うのは、集計結果をそのまま一時テーブルにする書き方です。

CREATE TEMPORARY TABLE tmp_member_total AS
SELECT member_id, SUM(amount) AS total, COUNT(*) AS cnt
FROM orders
GROUP BY member_id;

SELECT * FROM tmp_member_total;

MySQL 8.0.46 の実行結果です。

+-----------+-------+-----+
| member_id | total | cnt |
+-----------+-------+-----+
|       101 |  9000 |   3 |
|       102 |  3800 |   2 |
|       103 |   800 |   1 |
+-----------+-------+-----+

列を定義して作る・インデックスを張る

列の型やインデックスを自分で決めたいときは、通常の CREATE TABLE と同じ形で書きます。

CREATE TEMPORARY TABLE tmp_def (
  id   INT PRIMARY KEY,
  name VARCHAR(20),
  INDEX idx_name (name)
);

AS SELECT で作った一時テーブルにも、あとから主キーやインデックスを足せます。

ALTER TABLE tmp_member_total
  ADD PRIMARY KEY (member_id),
  ADD INDEX idx_total (total);

SHOW CREATE TABLE tmp_member_total;

CREATE TEMPORARY TABLE `tmp_member_total` (
  `member_id` int NOT NULL,
  `total` decimal(32,0) DEFAULT NULL,
  `cnt` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`member_id`),
  KEY `idx_total` (`total`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

SUM(amount) の列が decimal(32,0)、COUNT(*) の列が bigint と、AS SELECT では型が自動で決まります。 型を自分で決めたいときは、列定義つきで作ってから INSERT ... SELECT で入れます。

SHOW TABLESには出てこない

SHOW TABLES;

+----------------+
| Tables_in_t180 |
+----------------+
| orders         |
+----------------+

一時テーブルは SHOW TABLES にも information_schema.TABLES にも表示されません（どちらも orders だけが返りました）。自分の接続にある InnoDB の一時テーブルは、次のクエリで件数や列数を確認できます。名前は内部名（#sql…）で表示されました（下の出力は4行のうち2行の抜粋です）。

SELECT NAME, N_COLS FROM information_schema.INNODB_TEMP_TABLE_INFO;

+-------------+--------+
| NAME        | N_COLS |
+-------------+--------+
| #sqlaf8_b_f |      4 |
| #sqlaf8_b_b |      5 |
+-------------+--------+

定義を見たいときは SHOW CREATE TABLE 一時テーブル名 が手軽です。

別の接続からは見えない

一時テーブルを作った接続を開いたまま、別の接続から同じ名前を SELECT してみます。

-- 別の接続で実行
SELECT * FROM tmp_member_total;

ERROR 1146 (42S02) at line 1: Table 't180.tmp_member_total' doesn't exist

別の接続からは「テーブルが存在しない」というエラーになりました。 そのため、複数の人や処理が同時に同じ名前の一時テーブルを作っても、互いにぶつかりません。

同じ一時テーブルを1つのクエリで2回参照するとエラーになる

MySQL の一時テーブルで一番つまずきやすい点です。自己結合してみます。

SELECT a.member_id, b.total
FROM tmp_member_total a
JOIN tmp_member_total b ON a.total < b.total;

ERROR 1137 (HY000) at line 1: Can't reopen table: 'a'

サブクエリや UNION で2回使っても同じエラーになりました（別名を付けていない場合、メッセージにはテーブル名が出ます）。

SELECT * FROM tmp_member_total
WHERE total = (SELECT MAX(total) FROM tmp_member_total);

ERROR 1137 (HY000) at line 9: Can't reopen table: 'tmp_member_total'

SELECT member_id FROM tmp_member_total
UNION ALL
SELECT member_id FROM tmp_member_total;

ERROR 1137 (HY000) at line 10: Can't reopen table: 'tmp_member_total'

MySQL 8.0 リファレンスマニュアルの「TEMPORARY Table Problems」のページにも、同じクエリの中で一時テーブルを2回以上参照できない という制限と、回避策として CTE が挙げられています（今回このページを開いて確認しました）。

直し方は2つあります。

直し方1：同じ文の中で2回使うなら CTE にする。

WITH t AS (
  SELECT member_id, SUM(amount) AS total FROM orders GROUP BY member_id
)
SELECT a.member_id, a.total, b.total AS other_total
FROM t a JOIN t b ON a.total < b.total
ORDER BY 1, 3;

+-----------+-------+-------------+
| member_id | total | other_total |
+-----------+-------+-------------+
|       102 |  3800 |        9000 |
|       103 |   800 |        3800 |
|       103 |   800 |        9000 |
+-----------+-------+-------------+

直し方2：一時テーブルをもう1つ複製して、別の名前で結合する。

CREATE TEMPORARY TABLE tmp_copy AS SELECT * FROM tmp_member_total;

SELECT a.member_id, a.total, b.total AS other_total
FROM tmp_member_total a JOIN tmp_copy b ON a.total < b.total
ORDER BY 1, 3;

こちらも上と同じ3行が返りました。なお、PostgreSQL 16.15 では一時テーブルの自己結合はそのまま実行でき、同じ3行が返っています。この制限は MySQL 特有のもの です。

同じ名前の通常テーブルは一時的に隠れる

CREATE TEMPORARY TABLE orders (note VARCHAR(20));
SELECT * FROM orders;          -- 一時テーブル側が見える

Empty set (0.00 sec)

DROP TEMPORARY TABLE orders;   -- 一時テーブルだけを削除
SELECT COUNT(*) FROM orders;   -- 元のテーブルが再び見える

+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+

通常のテーブルと同じ名前で一時テーブルを作ると、その接続では一時テーブルが優先されます。 取り違えを防ぐため、一時テーブルには tmp_ などの接頭辞を付けておくのがおすすめです。

削除する（DROP TEMPORARY TABLE）

DROP TEMPORARY TABLE IF EXISTS tmp_copy, tmp_in_tx;

TEMPORARY を付けておくと、一時テーブルだけが削除の対象 になります。通常テーブルの orders に対して DROP TEMPORARY TABLE orders を実行したところ、次のエラーになり、通常テーブルはそのまま残りました。

ERROR 1051 (42S02) at line 35: Unknown table 't180.orders'

同じ名前で作り直すときの挙動も確認しました。

何度実行しても同じ状態から始めたいスクリプトでは、先頭に DROP TEMPORARY TABLE IF EXISTS を置いてから作成する と安定します。

トランザクションとの関係

START TRANSACTION;
INSERT INTO tmp_def VALUES (1, 'a');
CREATE TEMPORARY TABLE tmp_in_tx (x INT);
ROLLBACK;

SELECT COUNT(*) AS rows_after_rollback FROM tmp_def;    -- 0
SELECT COUNT(*) AS tmp_in_tx_exists FROM tmp_in_tx;     -- 0（テーブルは残っている）

MySQL 8.0.46（InnoDB）では、一時テーブルへの INSERT はロールバックで取り消され、トランザクション中に作った一時テーブルそのものはロールバック後も残りました。 PostgreSQL とは挙動が違うので、次の章と比べてみてください。

PostgreSQLで一時テーブルを作成する（ON COMMITの3種類）

PostgreSQL では TEMPORARY を TEMP と短く書けます。

CREATE TEMP TABLE tmp_member_total AS
SELECT member_id, SUM(amount) AS total, COUNT(*) AS cnt
FROM orders GROUP BY member_id;

一時テーブルは pg_temp_数字 という専用のスキーマに作られます。

SELECT schemaname, tablename FROM pg_tables
WHERE tablename IN ('orders', 'tmp_member_total');

 schemaname |    tablename     
------------+------------------
 public     | orders
 pg_temp_3  | tmp_member_total

別の接続から SELECT すると、MySQL と同じく存在しない扱いになりました。

ERROR:  relation "tmp_member_total" does not exist
LINE 1: SELECT * FROM tmp_member_total
                      ^

ON COMMITでコミット時の動きを選ぶ

3種類を同じトランザクションで作って比べました。

BEGIN;
CREATE TEMP TABLE tmp_drop (id INT) ON COMMIT DROP;
CREATE TEMP TABLE tmp_del  (id INT) ON COMMIT DELETE ROWS;
CREATE TEMP TABLE tmp_keep (id INT) ON COMMIT PRESERVE ROWS;
INSERT INTO tmp_drop VALUES (1);
INSERT INTO tmp_del  VALUES (1);
INSERT INTO tmp_keep VALUES (1);
COMMIT;

SELECT COUNT(*) AS del_rows  FROM tmp_del;
SELECT COUNT(*) AS keep_rows FROM tmp_keep;
SELECT COUNT(*) FROM tmp_drop;

PostgreSQL 16.15 の実行結果です。

 del_rows 
----------
        0

 keep_rows 
-----------
         1

ERROR:  relation "tmp_drop" does not exist
LINE 1: SELECT COUNT(*) FROM tmp_drop;
                             ^

ON COMMIT DROPはトランザクションの中で使う

psql の既定（自動コミット）のまま ON COMMIT DROP 付きで作ると、その文が終わった時点でコミットされるため、次の文ではもう存在しません。

CREATE TEMP TABLE tmp_a (id INT) ON COMMIT DROP;
SELECT * FROM tmp_a;

CREATE TABLE
ERROR:  relation "tmp_a" does not exist
LINE 1: SELECT * FROM tmp_a;
                      ^

ON COMMIT DROP は BEGIN; 〜 COMMIT; の間で作って使う、と覚えておけば迷いません。

ROLLBACKすると一時テーブルごとなくなる

BEGIN;
CREATE TEMP TABLE tmp_rb (id INT);
ROLLBACK;
SELECT * FROM tmp_rb;

ERROR:  relation "tmp_rb" does not exist

PostgreSQL では CREATE TABLE もトランザクションの対象なので、ロールバックすると作成自体が取り消されます（MySQL では残りました）。

インデックス・ANALYZE・LIKE

CREATE INDEX idx_tmp_total ON tmp_member_total (total);
ANALYZE tmp_member_total;

一時テーブルにも通常のテーブルと同じようにインデックスを張れます。一時テーブルは自動バキューム（autovacuum）の対象外として知られているため、データを入れたあとに自分で ANALYZE を実行しておくと、統計情報に基づいた実行計画が選ばれやすくなります。

既存テーブルと同じ列・主キーを持つ空の一時テーブルは LIKE で作れます。

CREATE TEMP TABLE tmp_like (LIKE public.orders INCLUDING ALL);

\d tmp_like で確認すると、列4つと "tmp_like_pkey" PRIMARY KEY, btree (order_id) が引き継がれていました。取り込み前のデータを一度受け止める作業テーブルに便利です。

同じ名前のテーブルと既存チェック

PostgreSQL には DROP TEMPORARY TABLE という書き方はなく、DROP TABLE を使います。一時テーブルだけを確実に指定したいときは pg_temp.テーブル名 と書きます。

SQL ServerとOracleの一時テーブル（未実行）

この章のコードは手元で実行していません。構文の全体像をつかむための参考として載せています。

SQL Server：#・##・テーブル変数

-- ローカル一時テーブル（作成した接続だけで見える）
CREATE TABLE #member_total (
  member_id INT PRIMARY KEY,
  total     INT
);
INSERT INTO #member_total
SELECT member_id, SUM(amount) FROM orders GROUP BY member_id;

-- SELECT の結果からそのまま作る
SELECT member_id, SUM(amount) AS total
INTO #member_total2
FROM orders GROUP BY member_id;

-- グローバル一時テーブル（すべての接続から見える）
CREATE TABLE ##shared_total (member_id INT, total INT);

-- テーブル変数
DECLARE @t TABLE (member_id INT PRIMARY KEY, total INT);
INSERT INTO @t SELECT member_id, SUM(amount) FROM orders GROUP BY member_id;

-- 削除（SQL Server 2016 以降）
DROP TABLE IF EXISTS #member_total;

SQL Server の一時テーブルは tempdb に置かれます。MySQL や PostgreSQL と違い TEMPORARY というキーワードは使わず、名前の先頭の # の数で種類が決まる のが特徴です。

Oracle：グローバル一時表とプライベート一時表

-- グローバル一時表：定義は一度作れば残り、データだけが接続ごとに分かれる
CREATE GLOBAL TEMPORARY TABLE gtt_member_total (
  member_id NUMBER PRIMARY KEY,
  total     NUMBER
) ON COMMIT PRESERVE ROWS;   -- 省略時は ON COMMIT DELETE ROWS

-- プライベート一時表（18c 以降）：定義ごと接続の中だけに存在する
CREATE PRIVATE TEMPORARY TABLE ora$ptt_member_total (
  member_id NUMBER,
  total     NUMBER
) ON COMMIT PRESERVE DEFINITION;   -- 省略時は ON COMMIT DROP DEFINITION

Oracle のグローバル一時表は「テーブルの定義は消えない」 点が、ほかの DB の一時テーブルと大きく違います。あらかじめ管理者が作成しておき、利用側は INSERT と SELECT だけを行う、という使い方が一般的です。

CTE・サブクエリ・ビューとの違いを実行結果で確かめる

早見表の内容を、PostgreSQL 16.15 で実際に確かめます。

CTEはその文が終わると消える

WITH t AS (SELECT member_id, SUM(amount) AS total FROM orders GROUP BY member_id)
SELECT * FROM t WHERE member_id = 101;

SELECT * FROM t;   -- 次の文では t は使えない

CTE は WITH を書いた SQL 文の中だけで有効です。次の文で同じ集計を使いたいときは、もう一度同じ WITH を書くか、一時テーブルにします。

CTEは「毎回計算し直す」ことも「1回だけ計算する」こともある

実行計画（EXPLAIN）で、CTE がどう扱われるかを見ます。

EXPLAIN (COSTS OFF)
WITH t AS (SELECT member_id, SUM(amount) AS total FROM orders GROUP BY member_id)
SELECT * FROM t WHERE member_id = 101;

            QUERY PLAN             
-----------------------------------
 GroupAggregate
   ->  Seq Scan on orders
         Filter: (member_id = 101)

CTE を1回だけ参照した場合は、外側の条件（member_id = 101）が CTE の中に入り込み、ふつうのサブクエリと同じ計画になりました。 一方、同じ CTE を2回参照すると次のようになります。

EXPLAIN (COSTS OFF)
WITH t AS (SELECT member_id, SUM(amount) AS total FROM orders GROUP BY member_id)
SELECT a.member_id FROM t a JOIN t b ON a.total < b.total;

              QUERY PLAN               
---------------------------------------
 Nested Loop
   Join Filter: (a.total < b.total)
   CTE t
     ->  HashAggregate
           Group Key: orders.member_id
           ->  Seq Scan on orders
   ->  CTE Scan on t a
   ->  CTE Scan on t b

2回参照した場合は、集計を1回だけ行い、その結果（CTE Scan）を2か所で読んでいます。 WITH t AS MATERIALIZED (...) と書けば、1回しか参照しない場合でも結果を先に確定させる計画（CTE Scan）になることも確認しました。CTE の書き方や再帰クエリの詳しい使い方は範囲が広いので、ここでは一時テーブルとの違いに絞っています。

ビューにはインデックスを張れない

CREATE VIEW v_member_total AS
SELECT member_id, SUM(amount) AS total FROM orders GROUP BY member_id;

CREATE INDEX ON v_member_total (total);

ERROR:  cannot create index on relation "v_member_total"
DETAIL:  This operation is not supported for views.

ビューは SELECT 文に名前を付けたもので、データを持ちません。検索のたびに元のテーブルを読むので、速さは元のテーブルのインデックスしだいです。結果を保存してインデックスを張りたいときは、一時テーブル（接続の中だけ）か、マテリアライズドビュー（全体で共有）が候補になります。

性能の違い：100万行の集計結果を繰り返し使う

100万行のテーブルを用意し、「会員ごとの合計」を自己結合で2回使う処理を、CTE と一時テーブルで比べました。

CREATE TABLE big_orders AS
SELECT g AS order_id, (g % 50000) + 1 AS member_id, (g * 7 % 9000) + 100 AS amount
FROM generate_series(1, 1000000) g;
ANALYZE big_orders;

-- CTE 版
WITH t AS (SELECT member_id, SUM(amount) AS total FROM big_orders GROUP BY member_id)
SELECT COUNT(*) FROM t a JOIN t b ON a.member_id = b.member_id + 1 WHERE a.total > b.total;

-- 一時テーブル版
CREATE TEMP TABLE tmp_big AS
SELECT member_id, SUM(amount) AS total FROM big_orders GROUP BY member_id;
CREATE INDEX ON tmp_big (member_id);
ANALYZE tmp_big;
SELECT COUNT(*) FROM tmp_big a JOIN tmp_big b ON a.member_id = b.member_id + 1 WHERE a.total > b.total;

psql の \timing で測った結果です（PostgreSQL 16.15、同じ結果 49649 件。1〜2回の計測値で、環境によって変わります）。

読み取れることは次のとおりです。

• 1回しか使わないなら CTE が手軽。一時テーブルは「作成＋インデックス＋ANALYZE」で約250 ms かかるので、1回だけなら CTE（約210〜296 ms）と大きな差はありません。
• 同じ中間結果を何度も使うなら一時テーブルが有利。2回目以降のクエリは約15 ms で、CTE 版は実行のたびに100万行の集計をやり直します。
• インデックスで1件を引くような使い方は一時テーブルの得意分野（0.4 ms と 64.3 ms）。

一時テーブルを使うか決める判断表

flowchart TD
    A[中間結果を使いたい] --> B{1つのSQL文の中だけで使う?}
    B -- はい --> C{その場で1回だけ?}
    C -- はい --> D[サブクエリ または CTE]
    C -- 何度も参照する --> E[CTE]
    B -- いいえ 複数の文で使う --> F{ほかの接続とも共有する?}
    F -- いいえ --> G[一時テーブル]
    F -- はい --> H[ビュー / マテリアライズドビュー]

つまずきやすいポイントと対処

接続プール（アプリケーションが接続を使い回す仕組み）を使っている場合、「接続を閉じたら消える」はアプリケーションの処理が終わったときとは限りません。 処理の最後に DROP TEMPORARY TABLE IF EXISTS（PostgreSQL は DROP TABLE IF EXISTS）を入れるか、PostgreSQL ならトランザクション内で ON COMMIT DROP を使うと、次の処理に持ち越されません。

よくある質問

Q. 一時テーブルと CTE はどちらが速いですか？ A. 使う回数によります。今回の計測では、1回だけ使うなら差は小さく、同じ中間結果を2回以上使う場合は一時テーブル（約15 ms）が CTE（約210〜296 ms）より短い時間で済みました。まず CTE で書き、同じ集計を何度も繰り返していると感じたら一時テーブルを検討する、という順番がおすすめです。

Q. 一時テーブルは自分で削除しなくてもよいですか？ A. 接続を閉じれば自動で消えます。ただし、接続プールを使うアプリケーションや長時間つないだままの接続では残り続けるので、使い終わったら明示的に DROP しておくと安心です。

Q. ほかの人が同じ名前の一時テーブルを作っても大丈夫ですか？ A. MySQL・PostgreSQL の一時テーブルと SQL Server の # 付きテーブルは接続ごとに別物なので、同じ名前でもぶつかりません。SQL Server の ##（グローバル一時テーブル）と Oracle のグローバル一時表の定義は共有されるため、名前の重複に注意します。

Q. 一時テーブルにも主キーやインデックスを付けられますか？ A. 付けられます。MySQL では ALTER TABLE ... ADD PRIMARY KEY・ADD INDEX、PostgreSQL では CREATE INDEX が通常のテーブルと同じように使えることを確認しました。

まとめ

• 一時テーブルは「作成した接続だけで見え、接続を閉じると消える」作業用テーブル。別の接続からは存在しない扱いになる。
• 作成構文は DB で違う：MySQL・PostgreSQL は CREATE TEMPORARY TABLE、SQL Server は #名前、Oracle はグローバル一時表とプライベート一時表。
• PostgreSQL は ON COMMIT で寿命を選べる（PRESERVE ROWS／DELETE ROWS／DROP）。ON COMMIT DROP はトランザクションの中で使う。
• MySQL の一時テーブルは同じクエリで2回参照すると ERROR 1137: Can't reopen table。CTE にするか、複製して結合する。
• CTE はその SQL 文だけで有効、ビューは定義が残って共有できる、一時テーブルはデータを持ちインデックスを張れる。
• 同じ中間結果を何度も使うなら一時テーブル、1つの文で完結するなら CTE が目安。
• 後片付けは DROP ... IF EXISTS を習慣に。接続プールでは接続が使い回される点に注意する。

株式会社RJCでは、一時テーブルやCTEの使い分けを仲間と学び合っています。こうした知識を共有しながら、日々技術を磨いています。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。