Learn Sql Server   Learn Sql Theory   Learn Sql

 
Chapter 1
Chapter 2
Chapter 3
Chapter 4
Chapter 5
Chapter 6
Chapter 7
Chapter 8

Stuff function in Sql Server

comma separated values in sql server

OUTPUT Clause with INSERT,UPDATE, DELETE

Deleting Rows in Chunks

Combining Result Sets with UNION

Using the TABLESAMPLE to Return Random Rows

Summarizing Data Using WITH CUBE

Using GROUPING with WITH CUBE

Summarizing Data with WITH ROLLUP

Adding a Column to an Existing Table

Changing a Column Definition

Dropping a Table Column

Sql Server Case Sensitive

CreateTable with a Primary Key

Adding a Primary Key Constraint to an Existing Table

Creating a Table with a Foreign Key in Sql Server

Adding a Foreign Key to an Existing Table

Sql Server Rebuilding Indexes

Rename a Column Name or Table Name

Displaying the Oldest Active Transaction

using group by all

Selectively Query Grouped Data Using HAVING

Sql Table Variables

Introduction to Locking in SQL Server

Viewing Lock Activity in Sql

Sql Server Isolation Levels

Using SET TRANSACTION ISOLATION LEVEL

Sql Server Blocking

Using Set Lock Timeout

Sql Server Deadlocking

Setting Deadlock Priority

Chapter 9
Chapter 10
Chapter 11
Chapter 12
Chapter 13
Chapter 14
Chapter 15
Chapter 16

Combining Result Sets with UNION in Sql Server

In this topic we will learn how to use union operator to combine result sets with some example.UNION operator in Sql is used with Select statement.

The UNION operator is used to append the results of two or more SELECT statements into a single result set. Each SELECT statement being merged must have the same number of columns, with the same or compatible data types in the same order, as this example demonstrates:

SELECT SalesPersonID, GETDATE() QuotaDate, SalesQuota
FROM Sales.SalesPerson
WHERE SalesQuota > 0
UNION
SELECT SalesPersonID, QuotaDate, SalesQuota
FROM Sales.SalesPersonQuotaHistory
WHERE SalesQuota > 0
ORDER BY SalesPersonID DESC, QuotaDate DESC

This UNION Operator returns the following results


SalesPersonID QuotaDate SalesQuota
290 2005-02-27 10:10:12.587 250000.00
290 2004-04-01 00:00:00.000 421000.00
290 2004-01-01 00:00:00.000 399000.00
289 2004-01-01 00:00:00.000 366000.00
289 2003-10-01 00:00:00.000 566000.00
268 2002-01-01 00:00:00.000 91000.00
268 2001-10-01 00:00:00.000 7000.00
268 2001-07-01 00:00:00.000 28000.00

Union All Example:

SELECT col2 FROM UnionTest1
UNION ALL
SELECT col4
FROM UnionTest2

Result by union all:

col2
AAA
BBB
CCC
CCC
DDD
EEE


How Combining Result Sets with UNION Works

In the above examples of select statements with union we can say that The UNION operator allows you to combine the results of two or more SELECT statements into a single result set. This query appended two result sets into a single result set. The first result set returned the SalesPersonID, the current date function and the SalesQuota. Since GETDATE() is a function, it doesn’t naturally return a column name—so a QuotaDate column alias was used in its place:

SELECT SalesPersonID, GETDATE() QuotaDate, SalesQuota
FROM Sales.SalesPerson
The WHERE clause filtered data for those salespeople with a SalesQuota greater than zero:
WHERE SalesQuota > 0


The next part of the query was the UNION operator in Sql , which appended the distinct results with the second query:

UNION


The second query pulled data from the Sales.SalesPersonQuotaHistory, which keeps history for a salesperson’s sales quota as it changes through time:

SELECT SalesPersonID, QuotaDate, SalesQuota
FROM Sales.SalesPersonQuotaHistory
WHERE SalesQuota > 0


The Sql Server ORDER BY clause sorted the result set by SalesPersonID and QuotaDate, both in descending order. The ORDER BY clause, when needed, must appear at the bottom of the query and cannot appear after queries prior to the final UNION'd query. The Sql ORDER BY clause should also only refer to column names from the first result set:


ORDER BY SalesPersonID DESC, QuotaDate DESC


Looking at the results again, for a single salesperson, you can see that the current QuotaDate of ‘2005-02-27’ is sorted at the top. This was the date retrieved by the GETDATE() function. The other rows for SalesPersonID 290 are from the Sales.SalesPersonQuotaHistory table:
SalesPersonID QuotaDate SalesQuota
290 2005-02-2710:10:12.587 250000.00
290 2004-04-01 00:00:00.000 421000.00
290 2004-01-01 00:00:00.000 399000.00
290 2003-10-01 00:00:00.000 389000.00

Keep in mind that the default behavior of the UNION operator is to remove all duplicate rows, and display column names based on the first result set. For large result sets, this can be a very costly operation, so if you don’t need to de-duplicate the data, or if the data is naturally distinct, you can add the ALL keyword to the UNION:


UNION ALL


With the ALL clause added, duplicate rows are NOT removed.

Sponsored Links