SQL Server combine result sets

Question

I'm trying to combine the result sets of two different queries into a single result set. Is this possible without using a stored procedure, perhaps using the WITH keyword?

Take the following case:

SELECT * FROM student WHERE graduation_year = 2013 -- returns 140 rows
SELECT * FROM student WHERE graduation_year = 2014 -- returns 130 rows

Please ignore the obvious solution of suggesting an OR here as I'm leaving out the complex dynamic constraints just to perhaps illustrate my point.

I'd like to take the results from each of these queries individually and combine them into a single result set.

Taryn · Accepted Answer · 2012-12-13T16:54:23.283

2

You can use a UNION ALL between the two queries:

SELECT *, '2013' Source
FROM student 
WHERE graduation_year = 2013 
UNION ALL
SELECT *, '2014' Source
FROM student 
WHERE graduation_year = 2014

See SQL Fiddle with Demo

I added a field called source if you need to identify what dataset the row came from, this can be excluded if not needed.

If you want this in a temp table you can use some like this:

select *
into #temp
from
(
  SELECT *, '2013' Source
  FROM student 
  WHERE graduation_year = 2013 
  UNION ALL
  SELECT *, '2014' Source
  FROM student 
  WHERE graduation_year = 2014 
) src


select * from #temp

See SQL Fiddle with Demo

edited Dec 13 '12 at 16:54

answered Dec 13 '12 at 16:34

Taryn

242,637
56
362
405

Is there a way to name this result as a temporary table so I can query against it? – Adam Levitt Dec 13 '12 at 16:50
1

@AdamLevitt - fyi: Using a [CTE](http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx) would allow you to name the result and use it briefly without creating a temporary table. – HABO Dec 13 '12 at 17:41
Yes, I do want to do that, but I want to do it twice, and then use a third one to combine the results from the first two. – Adam Levitt Dec 13 '12 at 18:01

score 2 · Answer 2 · answered Dec 13 '12 at 16:35

2

You can use the UNION ALL

SELECT *
FROM student 
WHERE graduation_year = 2013 
UNION ALL
SELECT *
FROM student 
WHERE graduation_year = 2014

answered Dec 13 '12 at 16:35

Rahul Tripathi

168,305
31
280
331

score 1 · Answer 3 · answered Dec 13 '12 at 16:33

SELECT * FROM student WHERE graduation_year = 2013 -- returns 140 rows
Union All -- Retrieves all results from both recordsets including both duplicates
SELECT * FROM student WHERE graduation_year = 2014 -- returns 130 rows

SELECT * FROM student WHERE graduation_year = 2013 -- returns 140 rows
Union -- Retrieves all results from both recordsets discarding duplicates
SELECT * FROM student WHERE graduation_year = 2014 -- returns 130 rows

SQL Server combine result sets

3 Answers3