Probably the most common way of creating a table in SQL is to use a basic CREATE TABLE statement, along with the table’s definition. But that’s not the only way of doing it.

Below are five options for creating a table when using SQL.

A Simple CREATE TABLE Statement

First up, here’s an example of using a simple CREATE TABLE statement to create the table:

CREATE TABLE Pets
(
    PetId       int NOT NULL PRIMARY KEY,
    PetTypeId   int NOT NULL REFERENCES PetTypes (PetTypeId),
    OwnerId     int NOT NULL REFERENCES Owners (OwnerId),
    PetName     varchar(60) NOT NULL,
    DOB         date NULL
);

When using this method, we provide the full table definition. We can create the primary key and any foreign key constraints at the same time if we wish (we can alternatively add these later with the ALTER TABLE statement).

The CREATE TABLE … AS SELECT Statement

Another way to create a table is to base it on another table, or even the result of a query. We can use the CREATE TABLE … AS SELECT statement to do such a thing.

Here’s an example of creating a new table based on the table we created in the first example:

CREATE TABLE Pets2 AS
(SELECT * FROM Pets);

This creates a new table called Pets2 based on the Pets table. This method also inserts all data from the Pets table. However, it doesn’t include indexes and other column attributes.

If we didn’t want to insert the data, we could modify the query to return no results. For example:

CREATE TABLE Pets2 AS
(SELECT * FROM Pets WHERE 0 = 1);

Note that, although the CREATE TABLE ... AS SELECT statement conforms to the SQL standard, it is not supported by all DBMSs. Also, for those that do support it, there are variations around its implementation. Therefore, I’d suggest checking your DBMS’s documentation before using this option.

The SELECT INTO Statement

The SELECT INTO statement is a Sybase extension that can be used to insert the results of a query into a table (or a variable, depending on the DBMS):

SELECT * INTO Pets2
FROM Pets;

This example creates a table called Pets2 based on the table called Pets and inserts all data from Pets into Pets2.

In SQL Server and PostgreSQL, the SELECT INTO statement creates a new table and inserts the resulting rows from the query into it. In MariaDB it inserts the result set into a variable. In Oracle, it assigns the selected values to variables or collections. MySQL and SQLite don’t support the SELECT INTO statement at all.

The CREATE TABLE ... LIKE Statement

Some DBMSs support a LIKE clause in the CREATE TABLE statement. This enables us to base our new table on an existing one without moving any data:

CREATE TABLE pets2 LIKE pets;

This example creates a table called Pets2 based on the table called Pets, but no data was transferred. Pets2 is an empty table.

Some of the RDBMSs that support the CREATE TABLE ... LIKE statement include MySQL, MariaDB, and PostgreSQL.

The INHERITS Clause

Some DBMSs support an INHERITS clause that allows us to specify that the new table inherits its columns from one or more existing tables.

Here’s an example of using the INHERITS clause in PostgreSQL:

CREATE TABLE Pets2 (
    color varchar(60) NOT NULL
) 
INHERITS ( Pets );

Here, we created a new table called Pets2. We defined one column, and we inherited the rest of the columns from the Pets table.

We can use psql‘s \d command to look at the new table’s definition:

\d Pets2

Result:

                        Table "public.pets2"
  Column   |         Type          | Collation | Nullable | Default 
-----------+-----------------------+-----------+----------+---------
 petid     | integer               |           | not null | 
 pettypeid | integer               |           | not null | 
 ownerid   | integer               |           | not null | 
 petname   | character varying(60) |           | not null | 
 dob       | date                  |           |          | 
 color     | character varying(60) |           | not null | 
Inherits: pets

The difference between the INHERITS clause and the LIKE clause is that INHERITS creates a persistent relationship between the new child table and its parent table/s. Schema modifications to the parent/s normally propagate to children as well.

With the LIKE clause on the other hand, the new table and original table are completely decoupled after creation is complete, and therefore changes to the original table are not propagated to the new table.