SQL UNION

has average rating 8 out of 10. Total 19 users rated.

<<PreviousNext>>

Description

The SQL UNION operator combines the results of two or more queries and makes a result set which includes fetched rows from the participating queries in the UNION.

Basic rules for combining two or more queries using UNION :

1.) number of columns and order of columns of all queries must be same.

2.) the data types of the columns on involving table in each query must be same or compatible.

3.) Usually returned column names are taken from the first query.

By default the UNION behalves like UNION [DISTINCT] , i.e. eliminated the duplicate rows; however, using ALL keyword with UNION returns all rows, including duplicates.

Difference between SQL JOIN and UNION

1.) The columns of joining tables may be different in JOIN but in UNION the number of columns and order of columns of all queries must be same.

2.) The UNION puts rows from queries after each other( puts vertically ) but JOIN puts the column from queries after each other (puts horizontally), i.e. it makes a cartesian product.

Syntax

SELECT <column_list>t [INTO ]
[FROM ]  	[WHERE ]
[GROUP BY ]  	[HAVING ]
[UNION [ALL]
SELECT <column_list>
[FROM ]  	[WHERE ]
[GROUP BY ]  	[HAVING ]...]
[ORDER BY ]

The queries are all executed independently but their output is merged.

Sample table : product

Sample table : purchase

Example

SELECT prod_code,prod_name
FROM product
UNION 
SELECT prod_code,prod_name
FROM purchase;

Output

Pictorial Representation

Explanation

In the above example no clause have been added with UNION, so, by default UNION is acting as UNION [DISTINCT] and only the unique rows are available in the result set.

SQL UNION ALL

SELECT prod_code,prod_name,com_name
FROM product
UNION ALL
SELECT prod_code,prod_name,com_name
FROM purchase;

Output

Explanation

In the above example the optional clause ALL have been added with UNION for which, all the rows from each query have been available in the result set. Here in the above output the marking rows are non-unique but it has been displayed. If ignored ALL clause, the marking rows would have come once.

SQL UNION ALL using where

SELECT prod_code,prod_name,com_name
FROM product 
WHERE life>6
UNION ALL
SELECT prod_code,prod_name,com_name
FROM purchase 
WHERE pur_qty>10

Output

Explanation

In the above example the two queries have been set using two different criterias including WHERE clause. So all the retrieve rows (including duplicates) have displayed in the result set. Here in this example the marking rows are identical, but it has been displayed for the ALL clause along with UNION. If ignored ALL clause the marking rows would have come once.

SQL UNION a table to itself

SELECT prod_code,prod_name,com_name
FROM purchase 
WHERE pur_qty>6
UNION ALL
SELECT prod_code,prod_name,com_name
FROM purchase 
WHERE pur_amount>100000

SQL UNION a table to itself

Explanation

In the above example the two queries have been set using two different criterias for a same table. So all the retrieved rows ( including duplicates ) have displayed. Here in this example the marking rows are identical, but it has been displayed for the ALL clause along with UNION.

SQL UNION with different column names

SELECT prod_code,prod_name,life
FROM product
WHERE life>6
UNION
SELECT prod_code,prod_name,pur_qty
FROM purchase
WHERE pur_qty<20

Output

Explanation

In the above example the two queries have been set using two different criterias and different columns. The different columns in two statements are 'life' and 'pur_qty'. But as the data type are same for both the columns so, result have displayed. Usually returned column names are taken from the first query.

SQL UNION with Inner Join

SELECT product.prod_code,product.prod_name,
purchase.pur_qty, purchase.pur_amount  
FROM product
INNER JOIN purchase  
ON product.prod_code =purchase.prod_code
UNION
SELECT product.prod_code,product.prod_name,
purchase.pur_qty, purchase.pur_amount  
FROM product
INNER JOIN purchase  
ON product.prod_name =purchase.prod_name;

Output

Explanation

In the above example the union made by two queries. The queries are two inner join statement. In the first query the join take place between two tables where the prod_code of both tables are same and in the 2nd query the join take place between two tables where the prod_name of both tables are same.

SQL: Union vs Union All

The basic difference between UNION and UNION ALL is, UNION removes duplicate records but UNION ALL does not. Let apply these two commands on two tables table1 and table2.

Rows in table1 :

Rows in table2 :

UNION Example (Removes all duplicate records) :

select field1
from table1
UNION
select field1
from table2;
  

Output

UNION ALL Example :

select field1
from table1
UNION ALL
select field1
from table2;
  

Output

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

See our Model Database