Combine Results From Multiple Queries In SQL Server 2008

In this article I tell you about how to combine two or more SQL queries using special operator known as set operators. At first sight this looks similar to SQL joins although there is big difference. SQL joins are used to combine columns while set operators are used to combine rows from different queries with strong preconditions - all involved SELECTS must.

Three Set Operators are given here:

• UNION OPERATION
  
• INTERSECT OPERATION
  
• EXCEPT OPERATION
  

Union Operator

Union of two tables returns the table containing those values which are present in both the tables. It combines the results of two SQL queries into a single table of all matching rows and all duplicate records are eliminated.

Example

Lets see an example to return only unique rows. Union UserName from two tables i.e. Users and Users1.

Select UserName from table Users.

select UserName from Users

Output:

Select UserName from table Users1.

select UserName from Users1

Output:

To select all the Users who are present in both table Users and Users1 , we will take union of the above two queries, which would fetch the required result.

(select UserName from Users)

union all

(select UserName from Users1)

Output:

Intersect Operation

Intersection of tables result in a new table contains only those values which are common in both the tables.

Select UserName which are present in both the tables.

Except Operation

This function behaves like difference operation of relation algebra. It returns those values which are present only in the first table that do not have the corresponding record in the second table.

Select UserName which are in first table only.