How To Create a Table Like Another Table in MySQL
Published: 2023-03-20 10:32:51

**TL;DR: Learn everything you need to know about how you can create a table like another table in MySQL with the CREATE TABLE SELECT or CREATE TABLE LIKE query**

In this article, you will learn how you can create a new table like another table using a single MySQL query. This is possible thanks to the `CREATE TABLE ... SELECT` and `CREATE TABLE ... LIKE` statements. These two MySQL variants of `CREATE TABLE` can help you copy a table definition into a new table.

Let's now dig into how to create a new empty table like another table in MySQL.

## Can You Create a Table Like Another Table in MySQL?
The short answer is, “yes, you can!” In detail, you have two approaches to creating a table like another table in MySQL:

1. [`CREATE TABLE ... SELECT`](https://dev.mysql.com/doc/refman/8.0/en/create-table-select.html): creates one table from the selected columns of another table.
2. [`CREATE TABLE ... LIKE`](https://dev.mysql.com/doc/refman/8.0/en/create-table-like.html): creates an empty table from the definition of another table.

As you can see, these two SQL statements allow you to create a table from another. At the same time, they serve two different use cases. Let’s now learn more about both. At the end of this article, you will know everything about those two statements.

## CREATE TABLE ... SELECT Statement
Let’s learn more about the `CREATE TABLE ... SELECT` statement in MySQL.

### What Is the CREATE TABLE ... SELECT Statement?
`CREATE TABLE ... SELECT` creates a new table with one column for each element specified in the SELECT query. You can use this SQL statement with the following syntax:

```
CREATE TABLE new_table [AS] SELECT * FROM original_table;
```
Where

- `new_table` is the name of the new table to create
- `original_table` is the name of the original table to execute the SELECT query on

You can also use this query to append columns read from the original table to an existing table. In detail, the columns specified in the SELECT statement will be appended to the right side of the existing table.

Keep in mind that `CREATE TABLE ... SELECT` also copies the data from the selected columns. In detail, it creates a new row for each row in `original_table`. The selected columns will have the values read from the original tables, while the other existing columns will be initialized with their default values.

If you only want to use `CREATE TABLE ... SELECT` to create a new table given the definition of another table, then you should add the `LIMIT 0` statement. In this case, the syntax becomes:

```
CREATE TABLE new_table [AS]
SELECT * FROM original_table
LIMIT 0;
```
Now, no data will be copied from original_table to new_table and no rows will be created.

Note that the CREATE TABLE ... SELECT statement does preserve the primary key info, indexes, triggers, generated column info, foreign keys, or CHECK constraints specified in the original table.

### CREATE TABLE ... SELECT in Action
Let’s assume you have a `wp_country` table that contains all the countries in the world, as follows:
<br \>
![The wp_data table in DbVisualizer](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oqpqhtrm4c4hkqkls1i9.png)
<figure><figcaption>The wp_data table in DbVisualizer</figcaption></figure>
<br \>
Now, let’s launch a `CREATE TABLE ... SELECT` query:

```
CREATE TABLE countries
SELECT * FROM wp_country
LIMIT 0;
```
![Running a CREATE TABLE ... SELECT query in DbVisualizer](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1ktxat5i4yfnrx90lb6k.png)
<figure><figcaption>Running a CREATE TABLE ... SELECT query in DbVisualizer</figcaption></figure>
<br \>
You now have access to the countries table.
<br \>
![The countries table in DbVisualizer](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0ph523eegzrsxqmu7cyc.png)
<figure><figcaption>The countries table in DbVisualizer</figcaption></figure>
<br \>
Note that the new table has the same columns as `wp_country` with the same column attributes, but does not have a primary key. You can notice this by the fact that the `Null` attribute is not empty, while the `Key` section is empty for each column. This is because the `CREATE TABLE ... SELECT` statement keeps column attributes but does not preserve primary key info.

## CREATE TABLE ... LIKE Statement
Let’s dig into the `CREATE TABLE ... LIKE` statement in MySQL.

### What Is the CREATE TABLE ... LIKE Statement?
`CREATE TABLE ... LIKE` creates a new empty table based on the definition of another table. You can use this MySQL statement with the following syntax:

```
CREATE TABLE new_table LIKE original_table;
```
Where

- `new_table` is the name of the new table
- `original_table` is the name of the original table to copy the definition from

With `CREATE TABLE ... LIKE`, the destination table will preserve:

- any column attribute from the columns of the original table
- the primary key specified in the original table
- any index defined in the original table
- any generated column from the original table
- any CHECK constraint from the original table

At the same time, the CREATE TABLE ... LIKE MySQL statement will not preserve:

- any [DATA DIRECTORY](https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization.html) or INDEX DIRECTORY option set on the original table
- any foreign key definition specified in the original table
- any trigger associated with the original table

Keep in main that a `CREATE TABLE ... LIKE` query performs the same checks as a `CREATE TABLE` one. In other words, if the current SQL mode is different from the mode used when creating the original table, the table definition may be considered invalid for the new mode and cause the query to fail. Also, you cannot perform `CREATE TABLE ... LIKE` while a `LOCK TABLE` statement is running on the original table.

If you are not familiar with this, each table has a lock flag associated with it. MySQL uses these lock to prevent other client sessions from accessing a table for a limited time. In detail, a client session is the period of time between a client's connection to a MySQL database and its disconnection. Note that a client session can only acquire or release table locks for itself.

### CREATE TABLE ... LIKE in Action
Just like before, let's start from the wp_country table. This contains the list of all countries. Now, let’s assume you want to copy this table definition into a new table called `countries`.

You can achieve this with a `CREATE TABLE ... LIKE` query:

```
CREATE TABLE countries LIKE wp_country;
```
![Executing a CREATE TABLE ... LIKE query DbVisualizer](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iimlikbf0vhne8tdcpoq.png)
<figure><figcaption>Executing a CREATE TABLE ... LIKE query DbVisualizer</figcaption></figure>
<br \>
This is what the new `countries` table looks like:
<br \>
![Overview of the new countries table in DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cg4w2p4zvzajrorsxtbj.png)
<figure><figcaption>Overview of the new countries table in DbVisualizer.</figcaption></figure>
<br \>
As you can see, `wp_country` is empty but has the same primary key as the `countries` table. It also has the same column attributes.
<br \>
![Note the Null column attributes](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zd2khdwmc62dvxo3kzdc.png)
<figure><figcaption>Note the Null column attributes</figcaption></figure>
<br \>
This is because the `CREATE TABLE ... LIKE` statement preserves column attributes and primary key info. In other terms, that you can think of `CREATE TABLE ... LIKE` as an operation to copy the definition of a table, including all its characteristics but with no data. On the other hand, `CREATE TABLE ... SELECT` only performs a shallow copy of column names and data from a table to another. This is the main difference between the two SQL statements.

## Conclusion
In this article, you learned everything you need to know about how you can create a table like another table in MySQL. As you saw, MySQL offers two approaches to achieve this. `CREATE TABLE ... SELECT` allows you to copy columns from one table to another, including their data. At the same time, it does not preserve information related to primary keys or indexes.

On the other hand, `CREATE TABLE ... LIKE` enables you to create a new table from the definition of another table. This statement does not copy data, but includes info about primary keys, indexes, CHECK constraints, and check constraints.

Here, you also took a look at how you can run those queries in DbVisualizer. If you are not familiar with this tool, [DbVisualizer allows you to generate reference ER schemas automatically](https://www.dbvis.com/feature/references-graphs/). This helps you understand what columns a table consists of and how it is related to other tables. You can use this feature to visually understand how the new table created with CREATE TABLE ... SELECT or CREATE TABLE ... LIKE relates to existing tables. [Download and try DbVisualizer for free!](https://www.dbvis.com/download/)

Thanks for reading! We hope that you found this article helpful.

## About the author
Antonello Zanini is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing