In this article, we will explain the SQL UNION Operator through examples.
You can also check this tutorial in the following 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.
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
You can download the full source code of this example here: SQL UNION Operator
