SQL Tutorial

SQL UNION Operator

SQL UNION operator is one of the basic set operations in the relational database. SQL UNION operator allows you to combine the match result sets of two (or more than two) select queries into a single table. Two result sets of the select queries must be made up of same kind elements such as same number of column, and same data type (or automatically cast to each other) in all columns. In most implementations, the data type conversion will be occurred but it is very dependent-implementations. You can check it out your RDBMS to find out. The duplicated records of two result sets are eliminate except the UNION ALL is used.

The common syntax of SQL union is very simple as follows:

	query1
	UNION (ALL)
	query2

As an example here two tables which we will use to to demonstrate union operator

cost2006 table

CostCenterId  Cost  
------------  ------
           1  300000
           2  150000
           3  500000

cost2007 table

CostCenterId  Cost  
------------  ------
           1  120000
           2  250000
           3  450000

This is the query to union two table data

SELECT * , 2006 AS year
FROM cost2007
UNION
SELECT * ,2007 AS year
FROM cost2006

And the result is

CostCenterId    Cost  year  
------------  ------  ------
           1  120000    2006
           2  250000    2006
           3  450000    2006
           1  300000    2007
           2  150000    2007
           3  500000    2007

In order to sort the result, you can use ORDER BY to sort based on the column you've selected. The ORDER BY can be only in the last query. For example we can sort by CostCenterId as follows

SELECT * , 2006 AS year
FROM cost2007
UNION
SELECT * ,2007 AS year
FROM cost2006

ORDER BY CostCenterId

Here is the result which is sorted by cost center

CostCenterId    Cost  year  
------------  ------  ------
           1  120000    2006
           1  300000    2007
           2  250000    2006
           2  150000    2007
           3  450000    2006
           3  500000    2007

In a good database model one table has one and only one type of elements therefore SQL UNION does not make sense here. It is usually used in a data warehouse application in which the database models are not good nomalized.

 

Read On