SQL | Create Table Extension

SQL provides an extension for CREATE TABLE clause that creates a new table with the same schema of some existing table in the database.

• It is used to store the result of complex queries temporarily in a new table.
• The new table created has the same schema as the referencing table.
• By default, the new table has the same column names and the data type of the referencing table.

Syntax:

CREATE TABLE newTable LIKE pets

Example:

CREATE TABLE newTable as
            (SELECT * 
             FROM pets
             WHERE pets.BREED = 'German Shepherd')

Queries

pets table:

ID	Name	Breed	Gender
11441	Tommy	German Shepherd	Male
11442	Max	Beagle	Male
11443	Charlie	Pug	Male
11444	Daisy	Poodle	Female
11445	Zoe	Labrador	Female
11446	Toby	Bulldog	Male

Query 1:

CREATE TABLE newTable LIKE pets;
SELECT * 
FROM newTable 
where newTable.GENDER = 'Female';

Output:

ID	Name	Breed	Gender
11444	Daisy	Poodle	Female
11445	Zoe	Labrador	Female

Explanation: The newTable created is a copy of pets table. So, selecting female pets from newTable returns only two rows in which the pet is a female. Query 2 :

CREATE TABLE newTable as
            (SELECT * 
             FROM pets
             WHERE pets.BREED = 'German Shepherd');
SELECT * from newTable;

Output:

ID	Name	Breed	Gender
11441	Tommy	German Shepherd	Male

Explanation: First the inner query is evaluated and the results are stored in a new temporary relation. Following this, the outer query is evaluated which create newTable as add the output of inner query to newTable. References : Database System Concepts 6th Edition by Silberschatz