Open In App

How to Append Two Tables and Put the Result in a Table in SQL?

Improve
Improve
Like Article
Like
Save
Share
Report

Structured Query Language or SQL is a standard Database language that is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, SQL Server, PostGre, etc. In this article, we will learn how to append two tables and store the result into a new table using UNION, UNION ALL.

UNION:

Syntax:

SELECT column_one, column_two,..column_N INTO Table_name FROM table_name
UNION  SELECT column_one,column_two,column_three,.. column_N FROM table_name;

Union All:

Syntax:

SELECT column_one, column_two,column_three,.. column_N INTO Table_name FROM
table_name UNION SELECT column_one, column_two, column_three,..column_N
 FROM table_name;

The difference between Union and Union All is UNION doesn’t include duplicates, but UNION ALL includes duplicates too. Both are used with similar syntax.

Consider the following tables of two departmental stores data

Database: Shop1

Item_Id Name Count
1 USB drive 10
2 pencil 11
3 candle 01
4 sharpie 19
5 model car 12
6 water bottle 20

Command used to create the table

Query:

CREATE TABLE Shop1(Item_Id int,Name varchar(20),Count int)

Output:

Database: Shop2

Item_Id Name Count
1 nail file 11
2 rubber band 10
3 candle 01
4 pencil 10
5 model car 12
6 water bottle 12
7 bread 3
8 shoes 19
9 face wash 20

Command used to create the table

Query:

CREATE TABLE Shop2(Item_Id int,Name varchar(20),Count int)

Output:

Method 1: Using UNION Keyword

In order to join the two tables i.e. Shop1 and Shop2 we run the following command:

Query:

SELECT * INTO joined FROM Shop1 UNION SELECT * FROM Shop2;

The above command joins Shop1 and Shop2 into a new table joined which is as follows and can be viewed by the following command:

Query:

SELECT * FROM joined;
Item_Id Name Count
1 USB drive 10
1 nail file 11
2 pencil 11
2 rubber band 10
3 candle 1
4 pencil 10
4 sharpie 19
5 model car 12
6 water bottle 12
6 water bottle 20
7 bread 3
8 shoes 19
9 face wash 20

So the joined table doesn’t include duplicates as we have used UNION Keyword

Here is the output when we execute the query

Output:

Method 2: Using UNION ALL Keyword

Query:

SELECT * INTO joined2 FROM Shop1 UNION ALL SELECT * FROM Shop2;

The above command creates a new table names joined2 which includes all the values of Shop1 and Shop2.

We run the following command for viewing the table

Query:

SELECT * FROM joined2;
Item_Id Name Count
1 USB drive 10
1 nail file 11
2 pencil 11
2 rubber band 10
3 candle 1
3 candle 1
4 sharpie 19
4 pencil 10
5 model car 12
5 model car 12
6 water bottle 20
6 water bottle 12
7 bread 3
8 shoes 19
9 face wash 20

Here is the output when we execute the query.

Output:


Last Updated : 14 Sep, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads