What are set operators (Union) and how they differ from Joins in SQL

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How to create a Logon Trigger to control which users are allowed to connect to an Oracle Database - 13 January 2025
• How do we collect the actual execution plan from queries using Extended Event and how do we read its data - 2 December 2024
• How do we find what permissions a user has on an Oracle database - 1 November 2024

In this article we will analyze the use of set operators, the types that exist and the differences between them and SQL Joins.

In her world SQL (Structured Query Language) many times we will want to combine two different data sets into a new one. This is achieved by using set operators / union operator.

How is it different with SQL Joins?

Sta SQL Joins we compare columns from two different tables by making records consisting of the columns of those two tables.

Sta Union operators we join the records (lines) of two different result sets (queries) into a single one.

Set operators

There are four kinds of set operators: Union all, Union, Except, Intersect

Depending on which one we use will return a different result.

Let's explain them one by one with examples.

To begin with, we will make two tables with data so that it is easier to understand the difference between each type:

create table pelatis (
  onoma_pelati varchar(50));

create table pwlitis (
  onoma_pwliti varchar(50));
  
insert into pelatis values
('Stratos'),
('Nikos'),
('Iasonas');

insert into pwlitis values
('Stratos'),
('Giorgos'),
('Stefanos');

select * from pelatis;
select * from pwlitis;

Union All

In the Union All operator, we combine the results from several queries into a single one that also contains all records that are entirely duplicates.

select * from pelatis
union all
select * from pwlitis;

We see that “Stratos” appears twice as it exists in both sets of results.

Union

In the Union operator as opposed to Union All when we combine the results from several queries into a single one, not contains as many records as are entirely duplicates.

select * from pelatis
union
select * from pwlitis;

We see this time that “Stratos” only exists once, even though it exists in both sets of results.

Except

In the Except operator displays the first set of results after the results of the second have been removed.

select * from pelatis
except
select * from pwlitis;

Does not appear "Stratos" as it was present in the second set of results.

Intersect

The Intersect operator only displays those records that are the same in both result sets.

select * from pelatis
intersect
select * from pwlitis;

In this case only the registration "Stratos" is common to both sets of results.

Normal uses:

• The number of columns and their order should be the same in all queries.
• The data types should be compatible. (i.e. it is not possible to define the phone as text in the first query and as an integer in the second)

Sources:

• Microsoft Set – Union