Tutorial 38 - UNION and UNION ALL Video Lecture | Postgresql: A Complete Tutorial - Database Management

otherwise welcome to the PostgreSQL
industries I'm gonna tell you about the
Union and the Union all operator so
first me first let me tell you what is
actually a union operator so Union
operator is basically used to combine
the results of two or more select
statements that have duplicate rows or
either do not have to placate rows so
what I mean is that if I want to select
suppose two statements at a time I have
a statement that is a select star from
company and I have another statement
that a select star from Department now
what I need to do is I want to combine
the results of the two or tables or the
data that I get I have to combine the
two results so for that I will use the
Union operator or the Union all operator
so if you want to use the Union operator
so we should select exactly the same
number of columns the same data type or
but they're not they do not need to be
of the same length so what I mean here
is like suppose I have two tables that
is one is a company table and one is a
department table so that's the trend art
we have been following since the
beginning so for the Union operator to
work what I need to do is that suppose
I'm selecting ID name and Department
from my company table so what I need to
do in the department table is that I
need to select the ID Department and the
name from the department table as well
they need to be of the same type so for
example if I select name from here but I
do not have a name column here so this
would give me an error so what I need to
do is I need you to select the ID and
the Department the ID and the department
so that's all I can do in the Union
operator if I do something else that if
I select name and if I select something
else that is employee ID here it will
give me an error because it is not of
the same data right so you need to
remember this so the length could vary
but they need to be at the same time
that same type that's compulsion so what
is the difference
Union and union all is that in Union if
we have any duplicate rows that would be
you know a combined into a single rows
or we do not see any duplicate entries
if we use a Union operator while in case
of a Union all operator or the duplicate
rows we can see the duplicate rows and
they are a part of the results so let me
demonstrate it with to you with the help
of example so for example I am going to
do and you know join on the company and
the department table so what I do is
select company dot ID and name and
Department from company and I do an
inner join Department on company dr. ID
equal to Department dot P D so company
so what I see is only one result on
company that oh I need to do he and ESCO
ID because the ID means the serial
number and EMP ID means the employee ID
which we want so in this case you could
see that if I do an inner join I see the
result now suppose I want to do and left
outer join or something like that right
outer join or left outer join so for
this I need to just right left outer
join run it and you could see that I
have my results here that is ten results
now suppose that I want to perform an
inner join and
I left outer join simultaneously now I
need to combine the results of both of
these so in this case I will use the
Union operator that if you could do
enough join there now what I need to do
is I need to perform a union operator
and I need to do it for left outer join
so what it does is that it performs this
first then it performs this and it
combines the result of both of these so
if I run this I would see no change I
guess or yeah I have ten results now you
can see that this is a combination of
the inner join and the left outer join
so you could see here that I have same
columns same data type but a length
could vary so that's not a problem
and now to demonstrate Union also what
you need to do is just type Union all so
you would see that if we have any
duplicate entries in both of these
tables it will be shown now so we'll
just do and you see that number of
results have been turned out to be 12
now so you see that if I do Union all
you could find the duplicate rows that
is see this is a duplicate row and this
is a duplicate row so this is the
purpose of Union and union all so thank
you for watching this tutorial subscribe
for more and stay tuned