MySQL: CREATE TABLE AS Statement

This tutorial explains how to use the MySQL CREATE TABLE AS statement with syntax and examples.

Description

The MySQL CREATE TABLE AS statement is used to create a table from an existing table by copying the existing table's columns.

It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement).

Syntax

The syntax for the CREATE TABLE AS statement in MySQL is:

CREATE TABLE [ IF NOT EXISTS ] new_table [ AS ] 
  SELECT expressions
  FROM existing_tables
  [WHERE conditions];

Parameters or Arguments

IF NOT EXISTS

Optional. If specified, the CREATE TABLE AS statement will not raise an error if the table already exists.

new_table

The name of the table that you wish to create.

AS

Optional. Whether you specify the AS keyword or not has no impact on the creation of the table.

expressions

The columns from the existing_tables that you would like created in the new_table. The column definitions from those columns listed will be transferred to the new_table that you create.

existing_tables

The existing tables from which to copy the column definitions and the associated records (as per the WHERE clause).

WHERE conditions

Optional. The conditions that must be met for the records to be copied to the new_table.

Note

• The column definitions from the existing_tables will be copied to the new_table.
• The new_table will be populated with records based on the conditions in the WHERE clause.

Example

Let's look at a MySQL CREATE TABLE AS example that shows how to create a table by copying all columns from another table.

CREATE TABLE local_companies AS 
  SELECT *
  FROM companies
  WHERE state = 'Florida';

This example would create a new table called local_companies that included all columns from the companies table.

If there were records in the companies table, then the new local_companies table would be populated with the records returned by the SELECT statement.

Next, let's look at a CREATE TABLE AS example that shows how to create a table by copying selected columns from multiple tables.

For example:

CREATE TABLE suppliers AS
  SELECT companies.company_id AS "supplier_id", 
         companies.address, companies.state, categories.category_type
  FROM companies, categories
  WHERE companies.company_id = categories.category_id
  AND companies.state = 'Florida';

This example would create a new table called suppliers based on column definitions from both the companies and categories tables. Notice in this example that we have aliased the company_id field as supplier_id since we want the field in the new suppliers table to be called supplier_id and not company_id.