← SQL roadmap

Topic 10 of 52 · Intermediate

CREATE TABLE vs CREATE TABLE AS SELECT

Build a new table directly from a query's result.

01CREATE TABLE vs CREATE TABLE AS SELECT

Module 02 – Database & Table Management (DDL)

02What is it?

CREATE TABLE ... AS SELECT (CTAS) creates a new table whose columns and rows come from a SELECT query, in one statement. Plain CREATE TABLE only defines an empty structure.

03Why do we need it?

Snapshots, backups before risky changes, and materialising heavy aggregations into reporting tables are all one-liners with CTAS.

04Syntax

CREATE TABLE new_table AS
SELECT columns FROM source [WHERE ...] [GROUP BY ...];

05Parameter explanation

new_table
Name of the table to create.
SELECT ...
Any query; its column names/aliases become the new column names.
WHERE 1 = 0
Trick to copy only the structure (no rows).

06Sample database

Every example and exercise below starts from a fresh copy of this data.

CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
  emp_id    INT PRIMARY KEY,
  name      VARCHAR(60) NOT NULL,
  dept_id   INT,
  salary    DECIMAL(10,2),
  hire_date DATE
);
INSERT INTO departments VALUES (10,'Engineering'),(20,'Sales'),(30,'HR');
INSERT INTO employees VALUES
 (1,'Asha',10,85000,'2021-04-12'),
 (2,'Ravi',20,52000,'2022-01-03'),
 (3,'Meena',10,97000,'2019-09-20'),
 (4,'Karan',30,46000,'2023-06-15'),
 (5,'Divya',20,61000,'2020-11-30');

07Basic coding example

Basic example · runs on the real engine

08Step-by-step execution

  1. 1.The SELECT runs and finds 3 employees.
  2. 2.MySQL creates high_earners with columns emp_id, name, salary, types inferred from the source.
  3. 3.The 3 rows are inserted. Primary keys, indexes and foreign keys are NOT copied.

09Before and after output

Generated live by running the basic example against the sample database.

Running on the SQL engine…

10Intermediate coding example

Intermediate example

11Real-world business scenario

Before a risky UPDATE, an engineer runs CREATE TABLE orders_bkp_20240601 AS SELECT * FROM orders; so the data can be restored if something goes wrong.

Warehouses (BigQuery, Snowflake) use CTAS to materialise daily aggregate tables for dashboards.

12Common mistakes

CREATE TABLE t AS SELECT COUNT(*) FROM employees;

The column gets an awkward name like COUNT(*).

CREATE TABLE t AS SELECT COUNT(*) AS total FROM employees;

Expecting high_earners.emp_id to be a primary key

CTAS doesn't copy keys or indexes.

ALTER TABLE high_earners ADD PRIMARY KEY (emp_id); -- MySQL

CREATE TABLE employees AS SELECT * FROM employees;

Target already exists.

Use a new name such as employees_copy.

13Important comparisons

CREATE TABLECREATE TABLE AS SELECTCREATE TABLE LIKE (MySQL)
Rows copiedNoYesNo
Column typesYou defineInferred from queryExact copy
Keys/indexesYou defineNot copiedCopied
Typical useNew designSnapshot / aggregateEmpty clone

14Practical exercises

Beginner1. Copy the whole employees table to employees_copy.

Reveal solution (15)
Solution · run it

5 rows copied.

Beginner2. Create eng_staff with only department 10 employees.

Reveal solution (15)
Solution · run it

Asha and Meena.

Beginner3. Copy only the structure of employees into emp_empty.

Reveal solution (15)
Solution · run it

0 rows, same columns.

Intermediate4. Create name_list with name and hire year.

Reveal solution (15)
Solution · run it

MySQL: YEAR(hire_date).

Intermediate5. Create dept_totals with total salary per department.

Reveal solution (15)
Solution · run it

Aggregated snapshot.

Intermediate6. Create emp_dept joining names with department names.

Reveal solution (15)
Solution · run it

Denormalised reporting table.

Advanced7. Back up employees, give everyone a 10% raise, then restore from backup.

Reveal solution (15)
Solution · run it

Original salaries are back.

Advanced8. Create top_per_dept with the highest-paid employee in each department.

Reveal solution (15)
Solution · run it

3 rows.

16Key takeaways

  • CTAS = structure + data from a query.
  • Alias computed columns.
  • Keys and indexes are not copied.
  • WHERE 1=0 copies structure only.

17Knowledge check

1. Does CTAS copy the primary key?

2. How to copy structure without rows?

3. Where do CTAS column names come from?

Interview questions

CTAS vs INSERT INTO ... SELECT?

CTAS creates the table and fills it. INSERT INTO ... SELECT fills an existing table you created earlier (with your own keys and types).

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “CREATE TABLE vs CREATE TABLE AS SELECT” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.