Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Data Definition

SQL CREATE TABLE AS statement

Reference for SQL CREATE TABLE AS: build a new table from a SELECT query, what it copies and what it drops, and the SELECT INTO variants by dialect.

CREATE TABLE ... AS SELECT (often shortened to CTAS) creates a new table and fills it with the result of a query in a single statement. The column names come from the query, and the column types are derived from the expressions it returns.

It is handy for snapshots, reporting tables and scratch copies. The new table is an independent copy: later changes to the source tables do not reach it. For a result that should stay current, use a view instead.

SQL
CREATE TABLE top_products AS
SELECT id, name, price
FROM products
WHERE price > 50;

Try it

SQL Snapshot of expensive products ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL A summary table, and what CTAS does not copy ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] table_name [(column_name, ...)] AS
select_statement;
Part Description
table_name The new table. The statement fails if it already exists, unless IF NOT EXISTS is given.
(column_name, ...) Optional in PostgreSQL, MySQL and Oracle: names for the columns instead of the query's. Not accepted by SQLite.
select_statement Any query. Give expressions an alias (AS total), or the column name will be unhelpful.

Result: a new, populated table. Rows are copied once, at creation time.

Dialect differences

Dialect Behavior
SQLite CREATE TABLE t AS SELECT .... Column types are the declared affinity of the source column, or none for expressions. No constraints, defaults or indexes are copied.
PostgreSQL Same syntax; also SELECT ... INTO new_table FROM .... Add WITH NO DATA to copy only the structure.
MySQL Same syntax; you may add column definitions or keys before AS, and CREATE TABLE new LIKE old copies a table's full definition without data.
SQL Server No CREATE TABLE AS. Use SELECT ... INTO new_table FROM ....
Oracle CREATE TABLE t AS SELECT ...; WHERE 1 = 0 makes an empty copy.
SQL
-- SQL Server
SELECT id, name, price
INTO expensive_products
FROM products
WHERE price >= 100;

-- PostgreSQL: structure only
CREATE TABLE products_copy AS TABLE products WITH NO DATA;

Usage notes

  • No constraints come along. The copy has no primary key, foreign keys, NOT NULL, CHECK or defaults, and no indexes. Add them afterwards with CREATE INDEX or define the table first and use INSERT ... SELECT.
  • Types are inferred. A computed column such as price * 2 may end up with a looser type than you want. In other databases, cast explicitly.
  • It is a snapshot. Re-running needs a DROP TABLE first, or IF NOT EXISTS, which skips the whole statement (no new rows are added).
  • Empty copy: use a condition that is never true (WHERE 0 in SQLite, WHERE 1 = 0 elsewhere).
  • Large copies write every row, so they can be slow and need space; consider doing them in a transaction.