login

Subscribe to SQL Tutorial website RSS Feed
 » Advanced SQL Tutorial » SQL UNION 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 theUNION 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

Here is the result which is sorted by cost centers:

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.