Create a new table from a join of two tables with a join

Question

I have two tables with the same columns.

I can combine them with UNION

 select * from table1 union select * from table2; 

How to create a new table with the same contents and columns as this query?

Answer 1

You can use the CREATE TABLE ... SELECT statement .

 CREATE TABLE new_table SELECT * FROM table1 UNION SELECT * FROM table2; 

Answer 2

 create table new_table as select col1, col2 from table1 union select col1, col2 from table2 

Make sure you select the same set of columns from the joined tables.

Answer 3

Or even you can explicitly define the create table (we usually use in our project).

CREATE A TABLE IF THE AUDIT DOES NOT EXIST (

user_id varchar (30) NOT NULL default '',

user_ip varchar (255) NOT NULL default '',

........ ........

KEY ie1 (user_id))

union = (auditlog_2, auditlog_3, auditlog_4) engine = merge insert_method = last;