【SQL】CREATE TABLE AS SELECTを使用してテーブルを作成する方法

SQLのCREATE TABLE AS SELECTは、既存のテーブルからデータを抽出し、新しいテーブルを作成するための方法です。

この方法は、多くのデータベースシステムでサポートされており、データを選択して新しいテーブルに保存する際に役立ちます。

CREATE TABLE AS SELECTとは？

CREATE TABLE AS SELECT（CTAS）は、新しいテーブルを作成すると同時に、SELECT文を使って既存のデータをそのテーブルに挿入するためのSQL構文です。

構文

CREATE TABLE 新しいテーブル名 AS
SELECT 列1, 列2, ...
FROM 既存のテーブル名
WHERE 条件;

• CREATE TABLE
  新しいテーブルを作成します。
• AS
SELECT文を使って、新しいテーブルに挿入するデータを指定します。
• SELECT
  必要な列を指定します。
• FROM
  データを取得する元のテーブルを指定します。
• WHERE
  データを絞り込む条件を指定します（省略可能）。

既存のテーブルを上書きしたい場合は、CREATE OR REPLACE TABLE AS SELECTを使用することができます。

テーブルの内容を更新したいときに便利です。

CREATE OR REPLACE TABLE 新しいテーブル名 AS
SELECT 列1, 列2, ...
FROM 既存のテーブル名
WHERE 条件;

基本的な使用例

例えば、employeesという既存のテーブルから年齢が30以上の社員を抽出し、そのデータを新しいテーブルolder_employeesに保存したい場合、次のように記述します。

CREATE TABLE older_employees AS
SELECT employee_id, employee_name, age
FROM employees
WHERE age >= 30;

このSQL文では、employeesテーブルからemployee_id、employee_name、age列を抽出し、条件に合ったデータをolder_employeesという新しいテーブルに保存します。

列を一部だけ選択してテーブルを作成

元のテーブルの全ての列を使わずに、一部の列だけを選んで新しいテーブルを作成することができます。

これにより、必要なデータだけを新しいテーブルに保存することが可能です。

例えば、productsテーブルからproduct_idとproduct_nameだけを抽出して新しいテーブルproduct_summaryを作成する場合のSQL文は以下のようになります。

CREATE TABLE product_summary AS
SELECT product_id, product_name
FROM products;

条件を指定してテーブルを作成

条件を指定することで、特定のデータだけを抽出して新しいテーブルに保存することができます。

これは、大規模なデータセットから必要な部分だけを効率的に抜き出すのに役立ちます。

例えば、salesテーブルから2024年の売上データだけを抽出し、新しいテーブルsales_2024を作成する場合、次のように書きます。

CREATE TABLE sales_2024 AS
SELECT sale_id, sale_date, amount
FROM sales
WHERE YEAR(sale_date) = 2024;

テーブルの構造だけをコピー

データを挿入せず、元のテーブルの構造（スキーマ）のみをコピーすることも可能です。

これを行うためには、WHERE句で常にFALSEとなる条件を指定するか、SELECT文の結果をゼロ件にすれば良いです。

例えば、customersテーブルの構造をコピーして新しいテーブルempty_customersを作成する場合、次のようにします。

CREATE TABLE empty_customers AS
SELECT *
FROM customers
WHERE 1 = 0;

テーブル作成時の注意点

インデックスや制約のコピー

CREATE TABLE AS SELECTでは、元のテーブルにあるインデックスや制約（PRIMARY KEYやFOREIGN KEYなど）はコピーされません。

必要に応じて、新しいテーブルにインデックスや制約を手動で追加する必要があります。

例として、older_employeesテーブルに主キーを追加したい場合は、次のようにします。

ALTER TABLE older_employees ADD PRIMARY KEY (employee_id);

データ型の自動決定

新しいテーブルの列のデータ型は、SELECT文で選択された元の列に基づいて自動的に決定されます。

しかし、データベースによっては、期待するデータ型と異なる型が割り当てられることがあるため、必要に応じてデータ型を確認することをお勧めします。

まとめ

SQLのCREATE TABLE AS SELECTを使用すると、既存のテーブルから特定のデータを抽出し、それを新しいテーブルに保存することができます。通常のSELECT文同様、選択列、選択条件の指定も可能です。