7.3 CTAS (CREATE TABLE AS SELECT)

Level: 7 | Version: v1.1 | Author: Meptrasoft

Overview

CTAS stands for CREATE TABLE AS SELECT.

It creates a new table and fills it with the result of a SELECT query in one statement.

SELECT QUERY
     ↓
CREATE NEW TABLE
     ↓
COPY RESULT DATA

CTAS → Create a new table from query results

Important: CTAS copies the selected column definitions and data, but generally does not copy the original table's indexes, primary keys, foreign keys, or other constraints. Exact behavior can vary by database system.

CTAS — CREATE TABLE AS SELECT: Existing table to query to new table — Figure 1: CTAS creates a new table and populates it with the result of a SELECT query.

Syntax

CREATE TABLE new_table_name AS
SELECT column1, column2
FROM existing_table
WHERE condition;

The SELECT query determines what data is copied into the new table.

Practice Table

This topic reuses the employees table from 5.2 DDL.

emp_id	emp_name	department	salary
1	Ravi Kumar	IT	45000
2	Anjali Mehta	HR	38000
3	Suresh Rao	Finance	42000
4	Priya Nair	IT	50000

Example: CTAS

Create a new table containing only IT employees:

CREATE TABLE it_employees AS
SELECT emp_id, emp_name, salary
FROM employees
WHERE department = 'IT';

Now query the new table:

SELECT *
FROM it_employees;

Result:

emp_id	emp_name	salary
1	Ravi Kumar	45000
4	Priya Nair	50000

The new table contains the result of the SELECT query.

What Does CTAS Copy?

CTAS generally creates the new table based on the selected columns and their resulting data types.

Original Table
     ↓
SELECT columns + rows
     ↓
New Table

It does not normally copy the original table's indexes, primary key, foreign keys, or other constraints. If the new table needs those, they must be created separately.

When Is CTAS Useful?

CTAS is useful when you need to:

Create a table from query results
Make a temporary working dataset
Prepare transformed data
Create a reporting or analysis table
Quickly copy selected data

For example:

CREATE TABLE high_salary_employees AS
SELECT *
FROM employees
WHERE salary > 45000;

CTAS vs INSERT INTO

These commands serve different needs:

CTAS
→ CREATE NEW TABLE + INSERT QUERY RESULT

INSERT INTO
→ INSERT DATA INTO AN EXISTING TABLE

For example:

CREATE TABLE it_employees AS
SELECT *
FROM employees
WHERE department = 'IT';

creates the table from scratch.

Whereas:

INSERT INTO it_employees
SELECT *
FROM employees
WHERE department = 'IT';

requires it_employees to already exist in the database.

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
CTAS modifies the original table	It creates a new table
CTAS only copies data	It creates the new table structure and populates it with the query result
CTAS copies all indexes and constraints	These generally need to be created separately
CTAS and `INSERT INTO` are the same	CTAS creates the table; `INSERT INTO` requires an existing target table
The new table automatically stays synchronized with the source	CTAS creates a separate table; later source changes do not automatically update it

Placement Quick Points

CTAS
→ CREATE TABLE
→ AS SELECT
→ NEW TABLE + QUERY RESULT

CTAS stands for CREATE TABLE AS SELECT.
It creates a new table from a SELECT query result.
The original table is not modified.
Selected columns and data are copied into the new table.
Indexes and constraints generally need to be created separately.
The new table is independent of later changes to the source table.

Interview Questions

What is CTAS?: CTAS is a SQL technique that creates a new table and populates it with the result of a SELECT query in a single statement.
Does CTAS modify the source table?: No. It creates a separate new table.
Does CTAS copy indexes and constraints?: Generally, no. Primary keys, foreign keys, and indexes usually need to be created separately on the new table.
What is the difference between CTAS and INSERT INTO?: CTAS creates the target table and fills it with query results. INSERT INTO adds data to an existing table.
Is the new CTAS table automatically updated when the source table changes?: No. It is a completely separate table and does not automatically stay synchronized with the source table.

Practice & Hands-On Exercises

Using the employees table:

Create a table containing only IT employees:

CREATE TABLE it_employees AS
SELECT emp_id, emp_name, salary
FROM employees
WHERE department = 'IT';

Create a table containing employees with salary greater than 40000:

CREATE TABLE high_earners AS
SELECT *
FROM employees
WHERE salary > 40000;

Select the data from the new table:

SELECT * FROM high_earners;

Explain what CTAS creates.
Explain the difference between CTAS and INSERT INTO.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

SELECT
  ↓
CREATE TABLE AS
  ↓
NEW TABLE
  ↓
QUERY RESULT STORED

CTAS creates a new table and populates it with the result of a SELECT query in one statement.

End of CTAS (CREATE TABLE AS SELECT)