How to create a new table from other tables that are related to each other?

Question

I need to create a new table with all columns from other 4 tables.

My idea for creating the table would be to use the structure:

CREATE TABLE IF NOT EXISTS table_name AS SELECT ~select from other tables~

So far, so good. But I can't find a proper way to do the joins without the foreign key / primary key columns in the table repeating.

Is there a way to use something as distinct, but for columns, no for values?

I know I could do a simpler way, which would be to select exactly the fields I need (like select column.field1, column2.field2), but the 4 tables I need to use to create the new one, have several fields, would be a long code, I don't know if it would be the best way, I wanted something more optimized.

I was also wondering if using this select table structure as per the example would there be any way to set a primary key on creation?

I just tried typing the primary key (column) command after the end of select in various ways (as it would be if I was creating the table by normally setting the fields) and was unsuccessful. Creating the table with AS SELECT, can I only define primary and foreign keys with analter table after creation?

Answer 1

A sample:

USE test

-- Create source tables ...
CREATE TABLE table_1 (id INT, field_1 INT);
CREATE TABLE table_2 (id INT, field_2 INT);
CREATE TABLE table_3 (field_1 INT, field_3 INT);
CREATE TABLE table_4 (field_1 INT, field_4 INT);

-- ... and fill some sample data
INSERT INTO table_1 VALUES (1,1), (2,2);
INSERT INTO table_2 VALUES (1,11), (2,22);
INSERT INTO table_3 VALUES (1,111), (2,222);
INSERT INTO table_4 VALUES (1,1111), (2,2222);

-- Build SQL statement text ...
SELECT CONCAT('CREATE TABLE IF NOT EXISTS new_table AS',
              ' SELECT ', GROUP_CONCAT(fields),
              ' FROM table_1',
              ' NATURAL JOIN table_2',
              ' NATURAL JOIN table_3',
              ' NATURAL JOIN table_4;') INTO @sql
FROM ( SELECT CONCAT(MIN(table_name),'.',column_name) fields
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE table_schema = 'test'
       AND table_name IN ('table_1', 'table_2', 'table_3', 'table_4')
       GROUP BY column_name ) fields;

-- ... and execute it ...
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;

-- ... then check the result
SELECT * FROM new_table;

    mysql> SELECT * FROM new_table;
    +------+---------+---------+---------+---------+
    | id   | field_1 | field_2 | field_3 | field_4 |
    +------+---------+---------+---------+---------+
    |    1 |       1 |      11 |     111 |    1111 |
    |    2 |       2 |      22 |     222 |    2222 |
    +------+---------+---------+---------+---------+
    2 rows in set (0.00 sec)

-- Drop sample tables
DROP TABLE table_1;
DROP TABLE table_2;
DROP TABLE table_3;
DROP TABLE table_4;
DROP TABLE new_table;

Answer 2

(This is to address a Comment the OP made.)

When doing CREATE TABLE t SELECT ..., you can specify most (or all?) CREATE things. For example:

CREATE TABLE t (
    aa VARCHAR(123),  -- specify default length
    bb INT DEFAULT '0'  -- specify column not provided by SELECT
    PRIMARY KEY(aa)      -- add an index
) ENGINE=InnoDB
    SELECT 
        aa,    -- converted to VARCHAR(123),
        cc,    -- added to `t` with deduced datatype
    FROM xyz;

You selected 2 columns from xyz; got 3 columns in t, plus an index.