CODEWITHSUNDEEP
sqlsql-server-2008-r2union
superM
superM

Reputation: 8695

SQL condition on unioned tables

I'm trying to execute a query with union and then put an additional condition on the result. Basically, I want to have something like

SELECT * FROM (A UNION B) WHERE condition

Is this possible to do? When I try to execute

SELECT * FROM (A UNION B)

SQL Management Studio complains about the closing bracket:

Incorrect syntax near ')'.

Upvotes: 1

Views: 75

Answers (4)

Madhivanan
Madhivanan

Reputation: 13700

Use alias for derived table

SELECT * FROM (select * from A UNION select * from B) as t WHERE condition

Upvotes: 0

sasonic
sasonic

Reputation: 784

how about with CTE:

with tbl as (
   select * from a 
   union 
   select * from b) 
select * from tbl where condition

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269793

Here is the correct syntax:

SELECT *
FROM (select from A
      UNION
      select from B
     ) ab
WHERE condition;

I agree that the original syntax "looks right" for a from statement, but SQL doesn't allow it. Also, you need an alias on the subquery (requirement of SQL Server). Finally, you might consider using union all, if you know there are no duplicates in tables.

Upvotes: 1

Luc M
Luc M

Reputation: 17314

SELECT * 
FROM ( SELECT * FROM A
       UNION
       SELECT * FROM B
) temp 
WHERE condition

Upvotes: 2

Related Questions