MySQL Database Tutorial - 24 - UNION Video Lecture | MySQL (Sequential Query Language) Database Tutorial - Software Development

what is going on guys welcome to your
24th MySQL tutorial and guys I got some
good news for you you're just going to
go ahead and sit back breathe a sigh of
relief because in this lesson it's going
to be the first easy lesson it's going
to be really simple easy to understand I
just need to talk to you guys about
really a cool concept real quick and
that is something called unions so quite
often whenever you're making a website
you're going to need to run multiple
queries but you're going to want a
single result set because you know let's
say that you wanted to make a website
like eBay and you run it to run a query
that returned all the items over 190
bids so like all the really popular
items now you also wanted all the items
over $1000 regardless of bids maybe
you're making this for your homepage or
something you wanted to display the cool
items so all the items that have over
190 bids and also all the items over
$1000 regardless of the number of bids
so what you would do is you would do
something like this select the name cost
and bids from items where bids is
greater than 190 and remember our other
thing that we wanted is whenever we have
an item that klahn stupid Mouse whenever
we have an item that costs more than
$1000 I cannot type today I'm hitting
all the wrong keys here so basically
those are the two queries that we want
to run however we only want one results
that we only want one set of data
because you know it's just a lot easier
to work that way so whenever we would
run these queries right here we would
get a table for this and the table for
this well that's not good we only want
one set of data so in order to basically
take more than one query and end up with
only one result set just go ahead and in
between your queries type the word Union
that's it that's all that's basically
all this tutorial is this word right
here takes this query and this query it
runs it and it gives you one set of data
how awesome is that
and I know what you guys are saying
right now Buckey unions are absolutely
worthless because first of all you could
have done this and this and you could
have accomplished the same thing by just
using where or in an't statements well
it may look a little messy but the truth
is yes you could so why would you ever
use unions well first of all whenever
you're doing more complex filtering guys
I just showed you guys a really simple
example but in real life they aren't
this easy you're going to do complex
filtering and you're going to have to
use a bunch of different tables instead
of the same table and whenever you do
using unions is a whole lot more simple
cleaner and easier to manage so don't
forget about unions and also another
thing there are some times that you have
to use unions and you can't use where
clauses and I'll show you that in just a
second before we get to that I want to
mention this please don't forget this
remember since what we're doing is
basically taking multiple queries and
tying them together into one result set
whenever you use unions your columns
have to be the same you can't do
something like select name cost bids
from items and select name zip code
address postal address and state from
another table it won't work out because
your tables won't tie together so again
make sure that your columns are the same
so that whenever they unite they can tie
together nicely
now what I was talking about earlier on
whenever you use unions it automatically
MySQL automatically removes duplicate
entries so you know how we said okay
select the items that are greater than
190 bits or they cost more than $1000
well what if the case in this case the
baby seat where it costs more than $1000
and it has more than 190 bits well it
doesn't list it twice that only lists at
once you only see one baby seat here
so what if your boss said okay actually
this is like the home page of youtube or
something where you can have the
top-rated video and you can have the
most viewed or most watched video well
if you want to get a list that doesn't
remove the duplicate entries then just
go ahead
in use union all what this does is it
basically leaves those duplicate entries
and so now and go ahead and hit go and
check it out if this was the front page
of you know your website you would see
baby sweet seat twice once because it
costs more than a thousand dollars and
another time because it has more than
one hundred ninety bids so sometimes you
want this like I said if you're making a
website like YouTube and you know maybe
a video was the most favorited and the
most viewed they deserve both of those
honors so you know there you go so
anyways that's all you guys have for
this tutorial
and another thing before I let you guys
go whenever you use a union now that is
one instance where a where clause won't
do in you know that's it you basically
have to use Union all you can't
substitute it for anywhere but anyways
I'm going to shut up thank you guys for
watching don't forget subscribe and I'll
see you guys in the next video