Create Table As in MYSQL

In MySQL, you can use the CREATE TABLE ... SELECT statement to create a new table based on the result set of a query. This is similar to the CREATE TABLE AS statement in other database systems like Oracle.

Here's the syntax for CREATE TABLE ... SELECT in MySQL:

CREATE TABLE new_table_name

SELECT column1, column2, ...

FROM existing_table_name;

Here's an example:

Let's say you have an existing table called employees with columns employee_id, first_name, last_name, salary, and department_id, and you want to create a new table called high_salary_employees to store information about employees with a salary higher than $100,000.

You can use the CREATE TABLE ... SELECT statement as follows:

CREATE TABLE high_salary_employees

SELECT employee_id, first_name, last_name, salary, department_id

FROM employees

WHERE salary > 100000;

This statement will create a new table called high_salary_employees with the same columns as the employees table, and it will contain only the rows where the salary column is greater than $100,000.

It's important to note that the new table created with CREATE TABLE ... SELECT will not inherit constraints, indexes, triggers, or any other objects associated with the source table. You may need to recreate those objects on the new table if needed. Additionally, column data types and lengths will be inherited from the result set of the query, so it's important to ensure they are compatible with the data being inserted into the new table.

Here are five frequently asked questions about the `CREATE TABLE AS SELECT` statement in MySQL:-

1. What is the Purpose of the CREATE TABLE AS SELECT Statement in MySQL?

   The `CREATE TABLE AS SELECT` statement in MySQL is used to create a new table based on the result set of a query. It allows you to select data from one or more tables and insert it into a new table in a single operation.

2. What is the Syntax of the CREATE TABLE AS SELECT Statement in MySQL?

   The basic syntax of the `CREATE TABLE AS SELECT` statement in MySQL is:

   

   CREATE TABLE new_table AS

   SELECT column1, column2, ...

   FROM existing_table

   WHERE conditions;

   

   This statement selects columns from `existing_table` based on the specified conditions and inserts the result set into a new table named `new_table`.

3. Can I Create a Table Using CREATE TABLE AS SELECT Without Specifying an Existing Table?

   Yes, similar to Oracle's `CREATE TABLE AS SELECT` and MS SQL's `SELECT INTO`, you can create a table in MySQL using the `CREATE TABLE AS SELECT` statement without specifying an existing table. Instead, you directly specify the columns and their data from one or more tables or views.

4. Does CREATE TABLE AS SELECT Copy Indexes, Constraints, and Triggers?

   No, the `CREATE TABLE AS SELECT` statement in MySQL only copies the data from the source table or query result. It does not copy indexes, constraints, triggers, or any other database objects associated with the source tables. You would need to recreate these separately if required.

5. What Permissions Are Required to Use CREATE TABLE AS SELECT?

   To use the `CREATE TABLE AS SELECT` statement in MySQL, the user needs appropriate permissions to create tables in the database where the new table will be created. Additionally, if the statement involves selecting data from existing tables, the user needs permissions to read from those tables. Depending on the privileges granted, users may also need permissions to create objects in the database.