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