Video summary
In PostgreSQL, a UNION operation is fundamentally different from a JOIN because it combines rows vertically rather than columns horizontally. While joins merge data side-by-side based on common identifiers like primary or foreign keys, unions stack the results of two separate SELECT statements one above the other into a single output table. To demonstrate this concept, the video introduces an additional table containing extra Star Wars characters alongside an existing character info table. By selecting specific columns from both tables and using the UNION keyword, users can merge these datasets while automatically removing any duplicate rows that appear in identical positions across all selected columns.
The tutorial highlights two primary variations of this operation: standard UNION and UNION ALL. Standard UNION ensures uniqueness by eliminating duplicates; for instance, if a character like Luke Skywalker exists in both source tables with the same data, he will only appear once in the final result set. In contrast, UNION ALL preserves every row exactly as it appears in the input queries, including exact duplicates. This distinction is crucial depending on whether the goal is to create a clean list of unique entries or to analyze how frequently certain records occur across different datasets without filtering them out.
Beyond simple concatenation, unions offer significant flexibility for complex data manipulation tasks such as applying filters and sorting results. Users can include WHERE clauses in each SELECT statement to filter specific subsets of data before combining them, allowing for highly targeted queries that merge disparate information sources. However, there are important syntactic constraints to remember; specifically, the ORDER BY clause must be placed at the very end of the entire UNION query rather than within individual statements, as it applies only to the final combined result set. Additionally, unlike joins which require matching column types and often specific keys, unions do not validate data compatibility between tables beyond ensuring that each SELECT statement returns the same number of columns with compatible data types in corresponding positions.
Read the full video transcript
What's going on everybody? Welcome back
to another video. Today we are
continuing our Postgra SQL series
learning about unions. Now unions are
often compared to joins because you're
kind of combining two tables. But with a
join, you're putting columns side by
side into one output. But with a union,
you're putting rows of data one on top
of another. And in this lesson, we're
going to demonstrate how to do that.
Now, if you've been following along, we
have our character info. We have our
Star Wars characters. What we're going
to do is we're going to create another
table called character info extra. And
all we're going to do is run this. And
there's going to be some extra data in
here. Let's go ahead and run this. Let's
come right down here. We're going to
refresh this table. And now we have our
second table. Character info extra. We
can get rid of this. You can get this
down below. I'll have the code down
below. And I'll also put in a GitHub
link so you can download it. You can run
it like that if you prefer that way.
Now, let's copy this and let's look at
the difference between character info
and character info extra. Now, in our
character info, we have and let's bring
this up a little bit so we can see
everybody. We have 12 rows of data.
There's lots of great characters in
here, but we're going to pull this over.
And now we have lots of other people,
some newer characters, people like Finn
and Rey, and I guess Mace Window didn't
make the cut earlier on, but Mace Wind
is a huge character in Star Wars. And
you'll also notice that we have Luke
Skywalker again, which is odd because if
we look in our original table, we also
have Luke Skywalker. Now, that's
intentional because I'm going to use
that for demonstration purposes, but oh
my, that was an oversight. Now, let's
get into the actual union and see how
this works. A union is simply going to
put rows of data into one output
combined with rows of data from another
table or another output. So, we're going
to make two select statements. And we
can make this really easy. We can select
and let's just do character_ame.
And we're going to select just the
character name. We're going to do that
from character info. So, that's all
we're doing. Super simple. And then
we're going to say union. What are we
going to union? What are we going to
join these rows of data with? The exact
same thing except we're going to do
underscore extra. So we have this
character name in both tables. And all
we're doing is we're going to put all of
them into one output. So let's go ahead
and run this. And now we have all of the
character names right here. And let me
me position this a little better just so
we can see them all. And actually, it's
a lot of character names. So, I'm going
to pull it all the way up. We have a lot
of character names. And so, now there's
about 20 characters in here. And there's
a lot. And one thing you might notice,
or maybe you didn't notice, is that we
only have Luke Skywalker in here one
time, which is perfectly fine, right?
Luke Skywalker is only one character. He
should only be in here once. But there
are going to be times where maybe you
don't want to delete that duplicate
data, right? Maybe we want to keep the
duplicate data because maybe it's data
that can be duplicated and you want to
know how many times it's in there. Well,
all we have to do is come right here and
say union all. And let's go ahead and
run this. Now, if we pull this up,
you're going to notice that we have Luke
Skywalker right here. And then if we go
down, we also have Luke Skywalker down
here. Now, this is a very simple example
because we're just looking at one
column. But we can look at all the
columns or two or three or four specific
columns from each. Now, here's one thing
to note. It does not validate the data
at all. For example, if we come up here
and let's go look at this table really
quickly. Let's say we want to take uh
the species. We can come down here and
we can say specs
and let's run this.
And there we go. This does not validate
data does not validate whether these two
should go together or anything. So we're
just putting data in the same column and
that's it. And so the actual column
names don't matter like they do in
joins. With joins you have to join them
on a common ID, a common identifier,
primary key and a foreign key. union
doesn't care about that. And so you do
need to be a little bit more, you know,
careful because you don't want to
inadvertently put the wrong columns in
the wrong place and then you have really
messy data in your output. And so all
you have to do if you want to add more
columns though and let's just see what
columns we want to add is we can say
character name uh species and est oh boy
testing my uh spelling net worth. So,
we're going to take all these columns
from each one and we'll run this. And
then in our output, now we have
character names, species, and estimated
net worth. But now for all the tables,
not just one table. Now, there really is
kind of all you need to know for union
and union all. I will just say that
typically it's not this easy. Sometimes
you're doing filtering as well. And you
can do that. You can actually make these
entire full queries. You can say where
and then specify your filters. You can
also do order buys and you can group the
data and you can do all sorts of things.
One thing to note though is if you're
going to use an order by an order by
works on the last one. So I'm going to
say order by and let's come up here.
Let's do the estimated net worth and
let's do that. Whoops.
Let's do that descending. So we're going
to do highest to lowest. And let's get
rid of this wear this filter we were
going to add. But let's filter on the
order of estimated net worth. So it's
highest to lowest. If we put this filter
up here
and let's run this, you're going to get
a syntax error because the order by can
only come at the end. That's just a
small note. But besides that with union
and union all you can filter down your
data from this table, filter down your
data from this table and put it all in
one output with the data on top of each
other instead of like a join where it's
kind of side by side. So that is unions
in Postgra SQL. Be sure to mess around
with this. Try it out. Try different
columns. Try adding filters. This is how
you really get hands-on and you learn.
And so I hope this was helpful. I hope
that you learned something. If you did,
be sure to like and subscribe and I will
see you in the next lesson.