Select from union in SQL Server

Question

Is it possible to select from the result of a union? For example I'm trying to do something like:

SELECT A
FROM
(
    SELECT A, B FROM TableA
    UNION
    SELECT A, B FROM TableB
)
WHERE B > 'some value'

Am I missing anything or making an assumption about how this works? I'm using MSSQL 2005 so any solution will need to conform to what I can do there.

I receive the error 'Incorrect syntax near the keyword 'WHERE'.' and am unable to run the query. — BlargleMonster, Sep 13 '12 at 04:13
Yes it worked quite well, I was just waiting out the time requirement to accept your answer. Thank you! — BlargleMonster, Sep 13 '12 at 04:28

score 64 · Accepted Answer · answered Sep 13 '12 at 04:14

64

You should give alias to your table. So try this:

SELECT A
FROM
(
    SELECT A, B FROM TableA
    UNION
    SELECT A, B FROM TableB
) AS tbl
WHERE B > 'some value'

answered Sep 13 '12 at 04:14

Himanshu

31,810
31
111
133

score 5 · Answer 2 · answered Sep 13 '12 at 04:16

Give Alias name without which you will receive

Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'WHERE'.

Try this (Give Alias Name)

DECLARE @TableA Table(A int, B int)
DECLARE @TableB Table(A int, B int)

INSERT INTO @TableA VALUES(1,1)
INSERT INTO @TableA VALUES(1,2)
INSERT INTO @TableA VALUES(1,3)
INSERT INTO @TableA VALUES(1,4)

INSERT INTO @TableB VALUES(2,1)
INSERT INTO @TableB VALUES(2,2)
INSERT INTO @TableB VALUES(2,3)
INSERT INTO @TableB VALUES(2,4)

SELECT A
FROM
(
    SELECT A, B FROM @TableA
    UNION
    SELECT A, B FROM @TableB
)X -- here X is table Alias
WHERE B > 0

Result

A

Select from union in SQL Server

2 Answers2