sql

SQL UNION Operator

In this article, we will explain the SQL UNION Operator through examples.

You can also check this tutorial in the following video:

SQL Union Operator Tutorial – video

1. Introduction

SQL UNION operator combines results from two select statements into a single result which includes all unique rows. UNION ALL includes duplicated rows in the combined result. The syntax is as the following:

SELECT_QUERY_1 UNION [ALL] SELECT_QUERY_2

Note, the two SELECT queries must meet the following requirements:

  • The number and the order of the columns must be the same.
  • The data types of the corresponding columns must be the same or compatible.

The difference between UNION and UNION ALL is that UNION returns unique rows but UNION ALL returns all rows.

2. Technologies Used

The example code in this article was built and run using:

3. Create Database

In this step, I will create two tables and insert sample records.

3.1 Table User

I will create a user_tbl table and insert three records.

user_tbl

create table user_tbl(
   user_id INT NOT NULL AUTO_INCREMENT,
   user_fname VARCHAR(100) NOT NULL,
   user_lname VARCHAR(40) NOT NULL,
   PRIMARY KEY ( user_id )
);

insert into user_tbl(   user_fname ,   user_lname ) values('Mary','Zheng');
insert into user_tbl(   user_fname ,   user_lname ) values('Tom2','Zheng');
insert into user_tbl(   user_fname ,   user_lname ) values('Jerry2','Cheng');

3.2 Table Customer

I will create a contact_tbl table and insert three rows.

contact_tbl

create table contact_tbl(
   cnt_id INT NOT NULL AUTO_INCREMENT,
   cnt_fname VARCHAR(100) NOT NULL,
   cnt_lname VARCHAR(40) NOT NULL,
   PRIMARY KEY ( cnt_id )
);

insert into contact_tbl(   cnt_fname ,   cnt_lname ) values('Mary','Zheng');
insert into contact_tbl(   cnt_fname ,   cnt_lname ) values('Tom','Zheng');
insert into contact_tbl(   cnt_fname ,   cnt_lname ) values('Jerry','Cheng');

4. SQL UNION and UNION ALL

In this example, I will demonstrate how to use the UNION and UNION ALL operators to combine results.

4.1 Find Unique Results with UNION

In this step, I will use the UNION operator to combine two select statements into a single unique record set.

union query

select * from contact_tbl
union 
select * from user_tbl

The result has 5 unique rows.

4.2 Find All Results with UNION ALL

In this step, I will use the UNION ALL operator to combine two select statements’ results.

union all query

select * from contact_tbl
union all
select * from user_tbl

It returns 6 rows including duplicate records.

sql union - mysql queries
Figure 1 MySQL Queries

4.3 UNION with a Where Clause

The select query used in UNION follows the same syntax as a normal query. In this step, I will show a UNION with a Where clause example.

union with a where condition

select cnt_lname firstName, cnt_lname lastName from contact_tbl
where cnt_lname ='Zheng'
union 
select user_fname , user_lname  from user_tbl
where user_lname ='Zheng'

It returns three records.

5. Summary

In this step, I demonstrated the usage of Union and Union All operators:

  • Combined data with two select statements.
  • Demonstrated the difference between UNION and UNION ALL.

6. Download the Source Code

Download
You can download the full source code of this example here: SQL UNION Operator

Mary Zheng

Mary has graduated from Mechanical Engineering department at ShangHai JiaoTong University. She also holds a Master degree in Computer Science from Webster University. During her studies she has been involved with a large number of projects ranging from programming and software engineering. She works as a senior Software Engineer in the telecommunications sector where she acts as a leader and works with others to design, implement, and monitor the software solution.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button