How to create table3 from table1 and table2 in MySQL 5.7.12

Question

Consider tables table1, table2, table3

table1:

+------+------+
| col1 | col2 |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+

table2:

+------+------+
| col1 | col2 |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+

table3:

+------+------+
| col1 | col2 |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+

How do I create table3 out of table1 and table2 in MySQL 5.7.12? What is the name of this operation?

Here is code to create table1 and table2:

DROP TABLE table1;
CREATE TABLE table1 (
    col1 BIGINT,
    col2 TEXT
);
INSERT INTO table1 VALUES (1, 'a');
INSERT INTO table1 VALUES (2, 'b');
INSERT INTO table1 VALUES (3, 'c');
DROP TABLE table2;
CREATE TABLE table2 (
    col1 BIGINT,
    col2 TEXT
);
INSERT INTO table2 VALUES (2, 'b');
INSERT INTO table2 VALUES (3, 'c');
INSERT INTO table2 VALUES (4, 'd');

Using the UNION DISTINCT operation:

CREATE TABLE table3 SELECT * FROM table1 UNION DISTINCT SELECT * FROM table2;

mysql doesn't support full outer join. It will be union – Amit Verma Jul 25 '21 at 12:11 — Amit Verma, Jul 25 '21 at 12:11

score -1 · Accepted Answer · answered Jul 25 '21 at 12:16

-1

For inserting into table 3 you need

CREATE TABLE table3 (
    col1 BIGINT,
    col2 TEXT
);
insert into table3
select * from table1
UNION
select * from table2;

answered Jul 25 '21 at 12:16

Amit Verma

2,450
2
8
21

1

Not me, but some people automatically downvote answers to poor questions. This question is poor because the OP didn't make any attempt to solve it himself. – Barmar Jul 25 '21 at 12:19

How to create table3 from table1 and table2 in MySQL 5.7.12

1 Answers1