EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Union
 

MySQL Union

Roja Metla
Article byRoja Metla
EDUCBA
Reviewed byRavi Rathore

Updated June 6, 2023

MySQL Union

 

 

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: –

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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: –

mysql union output 1

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: –

mysql union output 2

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: –

mysql union output 3

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: –

mysql union output 4

select * from test_a
union
select * from test_b
union
select * from test_c

Output:

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: –

output 5

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: –

output 6

SELECT SUBJECT_NAME, MARKS
FROM
ST_MARKS
UNION
SELECT SUBJECT_NAME, MARKS
FROM
ST_MARKS_BKUP;

Output:

The screenshot is for the same: –

output 7

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.

  1. MySQL Index
  2. Datetime in MySQL
  3. MySQL count()
  4. MySQL Root

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW