Updated June 6, 2023
Introduction to MySQL Union
MySQL Union combines the output of two or more “SELECT” statements. The output of the “UNION” will be without duplicate rows. The number of fields in the table should be the same, and the data type of the columns should be the same. If you don’t want the duplicate rows to be ignored, we can do it with the “UNION ALL”. In the result set of the UNION table, the column name will be the same as the column name of the first select statement. In this session, let us learn more about the usage of the UNION and let see the examples of it: –
Syntax
Now let us see the syntax:
SELECT column_1, column_2,...column_n
FROM First_tables
[WHERE conditions]
UNION [DISTINCT]
SELECT column_1, column_2,...column_n
FROM Second_tables
[WHERE conditions];How Does MySQL Union Work?
Now let us create a sample table and see how the union works:
Table1
create table uniona
(
cola INT
);Table2
create table unionb
(
colb INT
);Insert data into the tables as below: – Below is for the “uniona” table.
insert into uniona values (99);
insert into uniona values (95);
insert into uniona values (94);
insert into uniona values (93);
insert into uniona values (92);
insert into uniona values (91);
insert into uniona values (99);Select the items for the table “uniona”: –
select * from uniona;Let us see the screenshot for the same: –
Insert data into the tables as below: – Below is for the “unionb” table.
insert into unionb values (99);
insert into unionb values (34);
insert into unionb values (35);
insert into unionb values (33);
insert into unionb values (32);
insert into unionb values (31);
insert into unionb values (30);
insert into unionb values (29);
insert into unionb values (28);Select the items for the table “unionb”: –
select * from unionb;Let us see the screenshot for the same: –
Now let us perform union operation on the above tables: –
select * from uniona
union
select * from unionb;Select the items for the table “uniona” and “unionb” and perform the union: –
Let us see the screenshot for the same: –
Example:
Now let us perform UNION on three tables as below: –
--Table1: -
create table TEST_A
(
cola INT
);
--Table2: -
create table TEST_B
(
colb INT
);
--Table3: -
create table TEST_C
(
colc INT
);Insert data into the above tables: –
insert into test_a values (1);
insert into test_a values (2);
insert into test_a values (3);
insert into test_a values (4);Let us see the columns of the table: –
select * from test_a;insert into test_b values (1);
insert into test_b values (5);
insert into test_b values (6);
insert into test_b values (7);
insert into test_b values (8);Let us see the columns of the table: –
insert into test_c values (1);
insert into test_c values (10);
insert into test_c values (11);
insert into test_c values (12);
insert into test_c values (13);Let us see the columns of the table: –
Output:
Screenshot for the above: –
select * from test_a
union
select * from test_b
union
select * from test_cOutput:
Here in the above output, we need to check two things: –
- The column name of the result set is the column name of the first “select” statement. Here it is “cola”.
- The second thing is that we can see that there is a duplicate row in all the tables of value “1”. But in the result set, UNION ignores the duplicate because we have only one row of data, “1”.
Screenshot for the same: –
Example of MySQL Union
Now let us see another example of real-time. Here we have “st_marks” and “st_marks_bkup”. “st_marks_bkup” is a backup table that has some data in it. Now, let us consider the backup table and check if all the rows have been inserted into the table “st_marks”. As we know that the UNION doesn’t give us duplicates the output of the tables should be rows of only one table: –
Actual Table:-
create table St_marks
(
subject_name varchar(20),
marks int
);
insert into st_marks values ('English', 98);
insert into st_marks values ('Mathematics', 93);
insert into st_marks values ('Physics', 78);
insert into st_marks values ('Chemistry', 67);
insert into st_marks values ('Art', 43);
insert into st_marks values ('Music Class', 67);
select * from st_marks;Backup table:-
create table St_marks_bkup
(
subject_name varchar(20),
marks int
);
insert into st_marks_bkup values ('English', 98);
insert into st_marks_bkup values ('Mathematics', 93);
insert into st_marks_bkup values ('Physics', 78);
insert into st_marks_bkup values ('Chemistry', 67);
insert into st_marks_bkup values ('Art', 43);
insert into st_marks_bkup values ('Music Class', 67);
select * from st_marks_bkup;Output:
A screenshot for the above is given below: –
SELECT SUBJECT_NAME, MARKS
FROM
ST_MARKS
UNION
SELECT SUBJECT_NAME, MARKS
FROM
ST_MARKS_BKUP;Output:
The screenshot is for the same: –
Conclusion
Things that need to remember are as below: –
- MySQL Union combines the output of two or more “SELECT” statements. The output of the “UNION” will be without duplicate rows.
- The number of fields in the table should be the same, and the data type of the columns should be the same. If you don’t want the duplicate rows to be ignored, we can do it with the “UNION ALL”.
- In the result set of the UNION table, the column name will be the same as the column name of the first select statement.
Recommended Articles
We hope that this EDUCBA information on “MySQL Union” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
