SQL: Creating a New Table from a SELECT Statement

In SQL, you can create a new table based on the results of a SELECT query. This technique is often used to extract and store specific data subsets, create backups, or transform data for analysis. This is achieved using the CREATE TABLE ... AS statement.

Syntax

The general syntax for creating a new table from a SELECT statement is:

• new_table: The name of the new table to be created.
• SELECT statement: Specifies the data to be included in the new table.
• WHERE condition: Optional; filters the rows to include in the new table.

Key Features

1. Structure and Data: The new table is created with the structure of the SELECT query and contains the data returned by the query.
2. No Primary Keys or Constraints: Constraints such as primary keys, foreign keys, and unique constraints are not copied to the new table. These need to be explicitly defined afterward.
3. Column Aliases: Aliases used in the SELECT query are reflected in the new table’s column names.

Examples

1. Copying an Entire Table

You can create a copy of an entire table by selecting all columns and rows:

• This creates a new table employees_copy with the same structure and data as employees.

2. Selecting Specific Columns

To create a table with only specific columns:

• The new table employee_names will only include the specified columns.

3. Filtering Rows

To create a table containing a filtered subset of data:

• Only rows where salary > 50000 are included in the new table high_salary_employees.

4. Using Aggregate Functions

You can use aggregate functions to summarize data while creating a new table:

• The new table department_salaries contains the average salary for each department.

5. Joining Tables

You can use a JOIN to create a new table based on data from multiple tables:

• The employee_departments table combines data from the employees and departments tables.

Important Considerations

1. Performance:
   • Creating a new table from a SELECT query can be resource-intensive, especially for large datasets.
   • Use filtering conditions (WHERE) and column selection (SELECT specific_columns) to minimize data and improve performance.
2. Indexing:
   • The new table does not inherit indexes from the source table. You need to define indexes manually if required.

   Example:

   sql

   CREATE INDEX idx_salary ON high_salary_employees(salary);

3. Constraints:
   • Constraints such as primary keys and foreign keys are not automatically transferred. Add these after creating the table.

   Example:

   sql

   ALTER TABLE employees_copy
ADD PRIMARY KEY (employee_id);

4. Temporary Tables:
   • If the new table is only needed for the duration of a session, consider using a temporary table:
     sql

     CREATE TEMPORARY TABLE temp_table AS
SELECT * FROM employees;


Advantages of Using CREATE TABLE ... AS

1. Quick Data Duplication: Efficiently copy tables for backups or experimentation.
2. Data Transformation: Extract and transform data into a new structure for analysis.
3. Intermediate Results: Store intermediate results of complex queries for further use.

The CREATE TABLE ... AS statement is a powerful tool in SQL for creating new tables based on query results. Whether you’re backing up data, filtering information, or creating summarized datasets, this technique offers flexibility and efficiency. Remember to define constraints and indexes on the new table as needed to ensure its functionality and performance.