Open In App
Related Articles

SQL | Create Table Extension

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Report issue
Report
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

Last Updated : 21 Mar, 2018
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads