Overview:
In this article I give you an overview on “set operations”. This article mainly focuses on setting basics right, based on which you can do some more R&D to become masters.
Introduction
“Set Operations”, if you are from Mathematical background then this is not a new word. So what are these set operations? Where can I use them? I am sure there would be some more questions in your mind on this.
Set Operations are used to combine multiple result sets into one single result set. There is BIG difference between “join” and “Combine”. Join is Horizontal operations and “Combine” is vertical operation. Let’s not get into joins.
Let’s go with simple example so that it will give you clear picture.
Basically there are 3 set operators available in SQL Server.
1) Union: This is to combine two or more result sets into single with or without duplicate.
2) Except: Takes the data from one result set where there is no matching in another.
3) Intersect: Takes the data from both the result sets which are in common.
One thing you need to make sure is when you are using set Operations the No. of columns should be same with the data type. There is no restriction on the column names.
Rules on Set Operations:
1) The column names or aliases must be determined by the first select.
2) Every select must have the same number of columns, and each lineup of columns must share the same data-type family.
3) Expressions may be added to the select statements to identify the source of the row so long as the column is added to every select.
4) ORDER BY clause should be part of the last statement, which orders the results.
Let’s get into working examples.
1) Union
As you know this is basically to combine multiple result sets into single with or without duplicate. The union operation is different from a join, In Mathematical terms; a union is addition, whereas a join is multiplication. Instead of extending a row horizontally as a join would, the union combines multiple result sets into a single result set.
Let’s look at the example:
SELECT EmployeeID,Name,Salary,DOJ from Employee
UNION
SELECT EmployeeID,Name,Salary,DOJ from Employee_HIST
We are combining the results from Employee and Employee_HIST table. When you use UNION operator it will eliminate the duplicate records meaning that if the same record is in both the tables then it will pickup the record from only one table.
Let’s look at another example:
SELECT EmployeeID,Name,Salary,DOJ from Employee
UNION ALL
SELECT EmployeeID,Name,Salary,DOJ from Employee_HIST
Here also we are combining the results from Employee and Employee_HIST table. When you use UNION ALL operator it will not eliminate the duplicate records meaning if you have the same record in both tables then in the final output you will see both the records.
UNION is always creates the performance issue. So when ever you are using UNION use it very judiciously. If you are not sure of what kind of data you have then you can use UNION. If you know you don’t have any duplicate records for which you want to combine the results then use UNION ALL.
2) Intersection
As you know this is basically to combine multiple result sets into single to fetch the common records in multiple result sets. Inner join finds common rows horizontally, while an INTERSECT finds common rows vertically.
Let’s look at the example:
SELECT EmployeeID,Name,Salary,DOJ from Employee
INTERSECT
SELECT EmployeeID,Name,Salary,DOJ from Employee_HIST
We are combining the results from Employee and Employee_HIST table. When you use INTERSECT operator it will give you the common records from both the tables meaning the records that are duplicated in both the tables.
3) Except
This is basically to return all the records from one result set where there is no matching in another table. This looks very similar to “Outer join” but join does horizontally and EXCEPT does vertically.
Let’s look at the example:
SELECT EmployeeID,Name,Salary,DOJ from Employee
EXCEPT
SELECT EmployeeID,Name,Salary,DOJ from Employee_HIST
With the above query we are fetching only the records which are in Employee but not in Employee_HIST.
Summary:
Normally set operations are costly operations so use it very judiciously.
 Attachments
Set Operations Image (19949-29184-Set Operations Img.doc)
|
| Author: Lakshmi 31 Jul 2008 | Member Level: Bronze Points : 0 |
Nice piece of information.
thank you for sharing your knowledge.
|