Create new table from two existing tables sql

To create a new table from two existing tables in SQL, you can use the SELECT statement with the INTO clause. Here is the general syntax:

SELECT column1, column2,...
INTO new_table_name
FROM table1
JOIN table2
ON table1.column = table2.column;

Here, column1, column2, etc. are the columns you want to include in the new table. table1 and table2 are the two existing tables you want to combine. new_table_name is the name you want to give to the new table.

For example, let's say you have two tables orders and customers with the following structures:

orders:
+---------+---------+--------+
| order_id | customer_id | order_date |
+---------+---------+--------+
| 1       | 1        | 2022-01-01 |
| 2       | 1        | 2022-01-15 |
| 3       | 2        | 2022-02-01 |
+---------+---------+--------+

customers:
+---------+----------+----------+
| customer_id | name     | email    |
+---------+----------+----------+
| 1        | John     | [email protected] |
| 2        | Jane     | [email protected] |
+---------+----------+----------+

You can create a new table order_customers that combines the customer_id and order_date columns from orders with the name and email columns from customers using the following query:

SELECT o.customer_id, o.order_date, c.name, c.email
INTO order_customers
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id;

This will create a new table order_customers with the following structure:

+---------+------------+----------+----------+
| customer_id | order_date | name     | email    |
+---------+------------+----------+----------+
| 1        | 2022-01-01 | John     | [email protected] |
| 1        | 2022-01-15 | John     | [email protected] |
| 2        | 2022-02-01 | Jane     | [email protected] |
+---------+------------+----------+----------+

Note that the INTO clause is used to specify the name of the new table, and the SELECT statement is used to specify the columns and data to be included in the new table. The JOIN clause is used to combine the data from the two tables based on the customer_id column.