Home » Enterprise Java » sql » UNION vs UNION ALL SQL Operators

About Yatin

Avatar photo
An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).

UNION vs UNION ALL SQL Operators

Hello. In this tutorial, we will learn UNION vs UNION ALL operators in SQL and will use the PostgreSQL database running on Docker.

1. Introduction

SQL stands for Structured Query Language and is used to extract and organize data stored in relational databases like MySQL, PostgreSQL, Oracle, etc. A relational database consists of rows and columns that allow fetching specific information from databases that can be used later for analysis. In real-time SQL manages a large amount of data that is written and read simultaneously and any query that reaches the SQL server is processed into three parts –

  • Parsing – Process to check the SQL query syntax
  • Binding – Process to check the SQL query semantics
  • Optimization – Process to generate the SQL query execution plan

1.1 Usage of SQL

Structured Query Language (popularly known as SQL) is commonly used by data analysts and data science professionals and is helpful too –

  • Execute queries against the database
  • Retrieve data from the database
  • Insert new records into the database
  • Update existing records in the database
  • Created stored procedures, functions, and materialized views in the database
  • Create users and grant permissions
  • Set permissions on tables, stored procedures, functions, and materialized views

1.2 UNION operator in SQL

  • The UNION operator selects rows from two or more tables
  • If rows of tables are the same those rows are included only once in the result set
  • The number of columns in the tables must be the same
  • Columns of the tables must be in the same order

1.3 UNION ALL operator in SQL

  • UNION ALL operator selects rows from two or more tables but does not ignore duplicate rows in the result set

2. SQL UNION and UNION ALL operators

Let us dive into some practice implementation on the PostgreSQL database.

2.1 Pre-requirement – Postgres Setup

Usually, setting up the database is a tedious step but with Docker, it is a simple process. You can watch the video available at this link to understand the Docker installation on Windows OS. Once done open the terminal and trigger the below command to set and run PostgreSQL.

Docker commands

-- command to run postgres on docker –

-- remember to change the password --
docker run -d -p 5433:5432 -e POSTGRES_PASSWORD= --name postgres postgres

-- command to stop the Postgres docker container --
docker stop postgres

-- command to remove the Postgres docker container --
docker rm postgres

Remember to enter the password of your choice. If everything goes well the PostgreSQL database server would be up and running on a port number – 5433 and you can connect with the Dbeaver GUI tool for connecting to the server.

Fig. 1. Postgres on Docker

2.2 Setting up pre-requisite data

Create the database named learning and add the following table and data to the tables with the help of the below SQL script.

Pre-requisite sql script

-- teachers
create table teachers (id serial primary key, name text not null, age int not null);
insert into teachers ("name", age) values ('adam', 31),('wilson', 25),('charles', 28);
select * from teachers;

-- students
create table students (id serial primary key, name text not null, age int not null);
insert into students ("name", age) values ('paul', 32),('allen', 25),('teddy', 23),('david', 27);
select * from students;

Once the SQL script is executed the mock data will be successfully added to the teachers and students tables respectively.

2.3 Playing with the UNION operator

Use the below SELECT keyword to understand the UNION SQL operator.

UNION operator

select age
from teachers
union 
select age
from students
order by age;

If everything goes well the result of the SQL query will be shown on the console and the usage of the UNION operator can be practically understood.

Fig. 2: UNION operator

2.4 Playing with the UNION ALL operator

Use the below SELECT keyword to understand the UNION ALL SQL operator.

UNION ALL operator

select age
from teachers
union all 
select age
from students
order by age;

If everything goes well the result of the SQL query will be shown on the console and the usage of the UNION ALL operator can be practically understood.

Fig. 3: UNION ALL operator

3. Summary

In this tutorial, we learned the brief of SQL UNION vs UNION ALL operators via different examples. You can download the SQL scripts used in this tutorial from the Downloads section.

4. Download the SQL scripts

This was a tutorial to understand UNION and UNION ALL in SQL.

Download
You can download the full source code of this example here: SQL UNION and UNION ALL operators

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

I have read and agree to the terms & conditions

 

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