SQL Server Set operators


SQL set operators allows combine results from two or more SELECT statements. At first sight this looks similar to SQL joins although there is big difference. SQL joins tends to combine columns i.e. with each additionally joined table it is possible to select more and more columns. SQL set operators on the other hand combine rows from different queries with strong preconditions

Set operators
Set operators combine the results of two queries into a single one. The following set operators aid SQL in joining queries to retrieve rows. Now let us discuss them in detail. SQL Server provides the UNION, EXCEPT and INTERSECT set operations which work on complete rows from two queries to generate one result set. They are drawn from the fundamental operations of set theory. SQL Server does not currently support an EXCEPT ALL or INTERSECT ALL.
• Union
• Union All
• Intersect
• Except


The Columns in the select statements joined using the set operators should adhere strictly to the norms mentioned below.

1. The queries which are related by a set operator should have the same number of columns and the corresponding columns must be of the same data type.
2. Such a query should not contain any column of type long.
3. The label under which the rows are displayed are those from the first select statement.
4. The ORDER BY clause can only be used on the final set


For example, a simple table Table1, Table2 contains the following:

 SELECT * FROM Table1 

Table 1

 SELECT * FROM Table2 

Table 2

Union
The union operator returns all distinct rows selected by either query. The following example combines the result of two queries with the union operator which eliminates duplicate rows.

SELECT Name FROM Table1
UNION
SELECT Name FROM Table2

Union


Union All
The "Union All" operator returns all rows selected by either query including duplicates. The following example combines the result with the "Union All" operator, which does not eliminate duplicate rows.

SELECT Name FROM Table1
UNION ALL
SELECT Name FROM Table2

Union All



UNION will take the extra step of scanning the result set and presenting you with ONLY distinct rows. So it is going to perform slower because it has to sort the result set. UNION ALL does not have to take the extra step of scanning the result sets and extracting only the distinct values, so UNION ALL is usually better performing than UNION, because it does not have to sort the result set.
So UNION ALL gives faster results.

The execution plans for those two queries shows.
Plan

But this does not mean you use UNION ALL in every scenario.

Intersect
Intersect operator returns only rows that are common to both queries.
The following example is illustrative of the above statement

SELECT Name FROM Table1
INTERSECT
SELECT Name FROM Table2

Intersect

Except
Except operator returns all distinct rows selected only by the first query and not by the second query.
The following example is illustrative of the above statement

SELECT Name FROM Table1
EXCEPT
SELECT Name FROM Table2

Except


While using the "ORDER BY" clause, it must follow the last select statement and the must order by integer and not by the columns name. Consider the following example
Select ordid,cusid from table 1 union itemid,product id from table 2 Order by 2
SELECT ID,Name FROM Table1
UNION
SELECT Id,Name FROM Table2 ORDER BY 2

Order By

The above example displays distinct rows selected by either query, ordered by the second column stated in both the queries. Since the column names are different in the above queries. We use an integer in the "Order by" clause instead of name.

Conclusion
INTERSECT, EXCEPT, and UNION are all set operators that act on a minimum of two queries. INTERSECT and EXCEPT can be re-written in several different ways, using joins or utilizing IN/NOT IN within the WHERE clause.


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: