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.
CREATE TABLE top_products AS SELECT id, name, price FROM products WHERE price > 50;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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 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,CHECKor defaults, and no indexes. Add them afterwards withCREATE INDEXor define the table first and useINSERT ... SELECT. - Types are inferred. A computed column such as
price * 2may end up with a looser type than you want. In other databases, cast explicitly. - It is a snapshot. Re-running needs a
DROP TABLEfirst, orIF NOT EXISTS, which skips the whole statement (no new rows are added). - Empty copy: use a condition that is never true (
WHERE 0in SQLite,WHERE 1 = 0elsewhere). - Large copies write every row, so they can be slow and need space; consider doing them in a transaction.