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
08Step-by-step execution
- 1.The SELECT runs and finds 3 employees.
- 2.MySQL creates high_earners with columns emp_id, name, salary, types inferred from the source.
- 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
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 TABLE | CREATE TABLE AS SELECT | CREATE TABLE LIKE (MySQL) | |
|---|---|---|---|
| Rows copied | No | Yes | No |
| Column types | You define | Inferred from query | Exact copy |
| Keys/indexes | You define | Not copied | Copied |
| Typical use | New design | Snapshot / aggregate | Empty clone |
14Practical exercises
Beginner1. Copy the whole employees table to employees_copy.
Reveal solution (15)
5 rows copied.
Beginner2. Create eng_staff with only department 10 employees.
Reveal solution (15)
Asha and Meena.
Beginner3. Copy only the structure of employees into emp_empty.
Reveal solution (15)
0 rows, same columns.
Intermediate4. Create name_list with name and hire year.
Reveal solution (15)
MySQL: YEAR(hire_date).
Intermediate5. Create dept_totals with total salary per department.
Reveal solution (15)
Aggregated snapshot.
Intermediate6. Create emp_dept joining names with department names.
Reveal solution (15)
Denormalised reporting table.
Advanced7. Back up employees, give everyone a 10% raise, then restore from backup.
Reveal solution (15)
Original salaries are back.
Advanced8. Create top_per_dept with the highest-paid employee in each department.
Reveal solution (15)
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).
Lesson test
Take an 8-question test on “CREATE TABLE vs CREATE TABLE AS SELECT” — concepts, output prediction, bug spotting and interview questions.