SQL Union | SQL Union All | Union vs Union All in SQL Server | Intellipaat


Hey guys welcome back to this SQL
tutorial course. In this session we will work with Union and union all operators.
So the Union operator is used to combine the results set of two or more select
statements. For example at the first select statement returns these four fish
and the second select statement returns these three fish then the Union operator
would return the result set of these two select statements and if there is a
record which is present in both the tables, we will get only one of them in the
final result. That is there would be no duplicates in the final result so here
we see that this blue fish is present in both the tables A and B but in the final
result of A union B well get only one of the value. And this is the syntax for the
Union operator. Will give the first select statement after that we’ll give
the Union operator following which will give the second select statement. Now you also need to keep in mind that the number and order of columns must be same in both the Select queries and we’ll be implementing the Union operator on these two tables. So we have the student details one table which has the columns
SID, S_name and S_marks and has these four records the other table is student
details to which has the same columns and comprises all of these three records
so let’s go to SQL server and work with the Union operator. So we have our
two tables right in front of us. So let me start with the syntax. I’ll give the
first select query, select star from and then I’ll give the name of the table
which is student details one, after that I’ll give the Union operator and then
give the next select statement, select star from and then the name of the
table student details 2. I’ll select all of this, I’ll hit execute so the
Union operator has given us the result set of both of the select statements. And we have the Union all operator, the Union all operator gives all the rows
from both the tables including the duplicates as well. So here since the
Bluefish is present in both the tables A and B, the final result of A
Union all B will also have the duplicate value. So now let me just add the all
keyword over here so I am using the Union all operator between these two
tables. Now I Il hit execute so we see that we also have the duplicate values. So
here these records were Ann and Julia are present, these are from the first
table and again here Ann and Julia so these records are from the second
table. So we have also got the duplicate records this time because we’ve used the
Union all operator. Now again let me remove this all keyword, let me it
execute so we see that there are no duplicates this time. So this is a
difference between Union and union all operators. So this is how we can work
with the Union and union all operators Thanks for attending the session, let’s
meet in the next class

One Reply to “SQL Union | SQL Union All | Union vs Union All in SQL Server | Intellipaat”

  1. Got a question on SQL union or union all? Do write it in the comment and you will get a response immediately by our expert. For MS SQL Server training & certification, call us at US: 1800-216-8930 (Toll Free) or India: +917022374614. You can also write us at [email protected]

Leave a Reply

Your email address will not be published. Required fields are marked *