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
UNIONoperator 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 ALLoperator 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.
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.
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.
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.
You can download the full source code of this example here: SQL UNION and UNION ALL operators
