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.
CREATE TABLE newTable LIKE pets
CREATE TABLE newTable as (SELECT * FROM pets WHERE pets.BREED = 'German Shepherd')
CREATE TABLE newTable LIKE pets; SELECT * FROM newTable where newTable.GENDER = '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;
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
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.