Union and union all in sql server Part 17 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to premium technologies I
am venket this is part 17 of sequel
server in this session we'll talk about
the purpose of Union and union all the
differences between them and finally
we'll also talk about the differences
between joins and unions Union and union
all operators in sequence server
I used to combine the result set of two
or more select queries let's understand
what we mean by this with an example we
have two tables here TBL India customers
and TBL UK customers and both of these
tables have identical columns
you know ID name email in both of the
tables now let's say if I issue a select
query against these two tables look at
this I am issuing two different select
queries and if I just execute them look
at the result I get two separate result
sets okay
in TBL India customers we've got two
rows and TBL UK customers we have got
two rows and I get these as two separate
result sets now let's say I want to
combine both of the result sets into one
result set how do I do that using Union
or Union all okay
let's first try to use Union all and see
what happens okay so select star from
TBL India customers table and then I'm
saying Union all select star from TBL UK
customers and when I execute this query
look at what's going to happen it's
going to combine the rows from TBL India
customers with the rose from TBL UK
customers so when I use this Union all
I'm combining the result sets from these
two select queries okay now instead of
using Union on if I just say Union let's
see what's going to happen okay look at
this I got only three rows but let's put
it back to Union all we get four rows
and if you look at the output here there
is a duplicate record here
number two Sam and email is SSS com
so when I use Union all I am getting all
the rows including the duplicates but
the moment I remove Union all and if I
just use you
Union look at what happens to Sam record
when I execute this you know we have
caught only one Sam record and look at
the output it is sorted as well okay but
whereas when we use Union all you know
it's basically not sorted it takes all
the rows from TBL India customers you
know combines them with the rose from
TBL UK customers and just gives the
output but whereas when you use Union
it's doing two things here it's zooming
removing duplicates and it's also
sorting the results okay
we'll talk about the differences between
Union and union all in the next slide so
if you look at the differences between
Union and union all Union removes
duplicate rows whereas Union all doesn't
remove them and another difference is
for Union to actually remove the
duplicates it has to perform a distinct
sort and then it has to remove the
duplicates so for this reason you know
Union is a little slower than Union all
and this distinct short operation is
actually resource intensive in the sense
it's time consuming okay if you want to
see that you can actually turn on the
estimated query execution plan in sequel
server management studio and to do that
you can either press ctrl L or you have
an icon here which displays an estimated
execution plan so display estimated
execution plan when we click that what's
going to happen is it's going to show us
the estimated execution plan look at
this in the display here the distinct
sort is actually taking 63% of the you
know time okay but whereas on the other
hand when I use Union all and we say
display estimated execution plan there
is no distinct to sort okay so it just
selects the records from TBL India
customers and then combines them with
the result from TBL UK customers and
just displace them but whereas when we
use Union it has to first sort the
records and then remove the duplicates
which
little timing time-consuming so for this
reason Union is a little slower than
Union all so these are the two
differences between Union and union all
Union removes duplicate rows whereas
Union all doesn't and Union is a little
slower than Union all because of the
fact that it has to perform that
distinct sort to remove duplicates and
this is a common interview question
that's asked these days okay so
obviously if you use Union we don't get
the duplicates whereas when you use
Union all we get the duplicates okay and
remember for Union and union all to work
the number data types and the order of
the columns in the Select statement
should be the same okay because and that
should make sense as well you know when
we actually execute this query look at
this you know if if we have to combine
the results of this select statement
with the results of this select
statement obviously the data types and
the number of columns has to be same
okay
on the other hand if you just say here I
want ID and name and if you say here I
want all the three columns ID name and
email okay now if I try to execute this
look at this looking at itself you can
say okay first select statement is
giving two columns whereas this one is
giving three columns how can we combine
them we can't do that so obviously when
we try to execute that we get an error
all queries combined using a Union must
have a unique and an equal number of
expressions in the in their target list
which means equal number of columns not
only that okay it's not enough if you
just have equal number of columns you
know they should be in the right order
you know instead of if you say you know
I want ID as the last column it doesn't
really make sense okay
how can you combine ID with email okay
you can't do that so when we execute
this look at this the conversion fail
okay because what's happening you know
it's trying to convert either this one I
mean name
to integer and it fails okay because it
detects this as an integer column and it
tries to convert this to integer and it
can't be the implicit conversion fails
and we get the error so the number of
the columns has to be the same the data
types have to be the same and they have
to be in the same order okay now you can
do this okay look at this
I'm saying OK I want ID you can say ID
and email and then name okay you can do
this when I execute this look at what's
going to happen okay I get the output
but if you look at the output emails are
combined with names and similarly here
names are combined with emails okay so
as long as the data types are similar it
tries to combine them you know even if
they are different it tries to do an
implicit conversion if the implicit
conversion works then it selects them
and combines them okay but if the
implicit conversion fails that's when we
get an error so remember the number of
columns and their data types have to be
similar and even the order has to be the
same you know if in your first query you
have the first column as integer in the
second query the first column has to be
integer if here it's an N where carry
this has to be an N back basically a
string one a string column okay so there
are data types and their orders have to
match and you need to have equal number
of columns otherwise the Union or Union
all doesn't work you get an error and we
have already spoken about the
differences between Union and union all
and sorting the results of a union or
Union all okay this is interesting and
we know that to sort the results you
know in sequel we use the order by
clause but let's say I have you know
let's use star for now
okay let's say I'm saying select star
from India customers union select star
from UK customers when we execute this
we get the results now let's say I want
to sort the results by name okay how do
I do that we use the order by class so
order by maybe name when we do that look
at this it's not sorted now but the
moment I execute this query it sorts the
results for us okay now if I use order
by on the first query let's see what is
going to happen now when I execute this
look at this it gives us an error
stating in correct syntax near the
keyword and it makes sense look at this
how does Union all works it takes the
rows from this table combines them with
the rows from this table now it doesn't
make sense to first take sorted rows and
then combine your result will again
become unsorted that's why after getting
all the results then sort the results
finally using your order by clause
that's why I think order by should be
only you know within the last select
statement for example I have another
table called TBL u.s. customers so when
I say Union all select star from TBL US
customers and we execute that it's going
to get all the rows from all the tables
and then it's going to sort them okay
but on the other hand if if you try to
use it on the second select you still
get an error you know okay so if you
want to solve the results of Union use
the order by Clause on the last select
statement like so alright and
differences between Union and join we
have spoken about joints in the previous
sessions in this video series
okay now unions and joints are two
separate things okay but however you
know these days people are asking this
question what's the difference between a
union and join okay join basically if
you look at this Union combines the
result set of two or more select queries
into a single result set which includes
all the rows from
all the queries in that Union where as
what what adjoin does is it retrieves
data from you know two or more tables
based on the logical relationship
between those two tables
so usually tables are related to each
other using primary and foreign keys
okay
so we will take you know the foreign key
and look up its value in the other table
and join the columns together in those
related tables whereas an Union you know
we basically combine the rows so in
short the difference between a union and
a join is that Union combined rows from
two or more tables
whereas join combines columns from two
or more Co you know tables for example
you know we have seen the examples of
Union we have taken rose from TBL USA
customers and join them with TBL UK
customer rose no we did a horizontal
kind of a joint whereas if you look at
you know basically not join it's a union
okay but whereas when you look at the
join for example we have TBL employees
table and TBL departments table and TBL
employees table we have caught
department ID column which we then look
up in the department's table and maybe
we retrieve the department name so from
TBL employees table I get employee name
and maybe his age and then from TBL
department table we get the department
name so enjoins we are combining the
columns between two or more tables
whereas in an Union we are combining
rows between two or more tables on this
slide you can find resources for asp.net
and C sharp interview questions that's
it for today thank you for listening
have a great day