Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Merge two tables with union in MySQL?
To merge two tables with UNION, you can use create table select statement. Following is the syntax −
create table yourNewTableName select * from yourFirstTableName UNION select * from yourSecondTableName;
Let us first create a table. Following is the query −
mysql> create table FirstTable -> ( -> Id int, -> PersonName varchar(20) -> ); Query OK, 0 rows affected (2.10 sec)
Following is the query to insert some records in the table using insert command −
mysql> insert into FirstTable values(10,'Larry'); Query OK, 1 row affected (0.12 sec) mysql> insert into FirstTable values(20,'David'); Query OK, 1 row affected (0.22 sec)
Following is the query to display all records from the table using select statement −
mysql> select * from FirstTable;
This will produce the following output −
+------+------------+ | Id | PersonName | +------+------------+ | 10 | Larry | | 20 | David | +------+------------+ 2 rows in set (0.00 sec)
Following is the query to create the second table −
mysql> create table SecondTable -> ( -> Id int, -> PersonName varchar(20) -> ); Query OK, 0 rows affected (0.91 sec)
Following is the query to insert some records in the table using insert command −
mysql> insert into SecondTable values(30,'Chris'); Query OK, 1 row affected (0.17 sec) mysql> insert into SecondTable values(40,'Robert'); Query OK, 1 row affected (0.15 sec)
Let us now display all records from the table using the select statement −
mysql> select *from SecondTable;
This will produce the following output −
+------+------------+ | Id | PersonName | +------+------------+ | 30 | Chris | | 40 | Robert | +------+------------+ 2 rows in set (0.00 sec)
Now, create a table from merging two tables (FirstTable + SecondTable) with union −
mysql> create table MergeBothTableDemo -> select * from FirstTable -> UNION -> select * from SecondTable; Query OK, 4 rows affected (0.86 sec) Records: 4 Duplicates: 0 Warnings: 0
Let us check the new table records. Following is the query −
mysql> select * from MergeBothTableDemo;
This will produce the following output −
+------+------------+ | Id | PersonName | +------+------------+ | 10 | Larry | | 20 | David | | 30 | Chris | | 40 | Robert | +------+------------+ 4 rows in set (0.00 sec)
483 Views
