Selenium WebDriver Java Framework Course Limited Time Offer for $20

Selenium WebDriver Java Framework Course Limited Time Offer for $20

 

Print

Combine tables with Union in SQL Statement

We can use Union or Union All in SQL statement to combine the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables.

 

UNION

Specifies that multiple result sets are to be combined and returned as a single result set.

UNION ALL

    Incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed.

In this example below, (1) we select top 5 rows from the Product table for ListPrice between 300 and 350 and store it as table #a (2) we select top 5 rows from the Product table for ListPrice between 400 and 600 (3) We combine table #a with table #b 

Method One (directly combine two result set from query)

Use AdventureWorks2008
Go

select top 5 ProductID,Name,ListPrice from Production.Product
where ListPrice between 300 and 350
Union
select top 5 ProductID,Name,ListPrice from Production.Product
where ListPrice between 400 and 600


ProductID	Name                 	ListPrice
722	LL Road Frame - Black, 58	337.22
723	LL Road Frame - Black, 60	337.22
724	LL Road Frame - Black, 62	337.22
725	LL Road Frame - Red, 44	337.22
726	LL Road Frame - Red, 48	337.22
731	ML Road Frame - Red, 44	594.83
732	ML Road Frame - Red, 48	594.83
733	ML Road Frame - Red, 52	594.83
734	ML Road Frame - Red, 58	594.83
735	ML Road Frame - Red, 60	594.83

Method 2 (use two temp tables to combine result set

--Alternative ways
----store top 5 products prices between 300 and 350 as table #a
select top 5 ProductID,Name,ListPrice,'from table #a' as source into #a from Production.Product
where ListPrice between 300 and 350
----store top 5 products prices between 400 and 600 as table #b
select top 5 ProductID,Name,ListPrice,'from table #b' as source into #b from Production.Product
where ListPrice between 400 and 600
--Combine table #a and table #b together
select * from #a
union
select * from #b

--Delete temp tables
drop table #a,#b
------------------------*/


ProductID	Name	                ListPrice  source
722	LL Road Frame - Black, 58	337.22	from table #a
723	LL Road Frame - Black, 60	337.22	from table #a
724	LL Road Frame - Black, 62	337.22	from table #a
725	LL Road Frame - Red, 44	        337.22	from table #a
726	LL Road Frame - Red, 48	        337.22	from table #a
731	ML Road Frame - Red, 44	        594.83	from table #b
732	ML Road Frame - Red, 48	        594.83	from table #b
733	ML Road Frame - Red, 52	        594.83	from table #b
734	ML Road Frame - Red, 58	        594.83	from table #b
735	ML Road Frame - Red, 60	        594.83	from table #b