Hi,
Im making select with union. select * from table_1 union select * from table_2 ...
Is it possible to filter query result by column values ?
thanks for help
+2
A:
Yes, you can enclose your entire union inside another select:
select * from (
select * from table_1 union select * from table_2) as t
where t.column = 'y'
You have to introduce the alias for the table ("as t"). Also, if the data from the tables is disjoint, you might want to consider switching to UNION ALL - UNION by itself works to eliminate duplicates in the result set. This is frequently not necessary.
Damien_The_Unbeliever
2010-10-29 10:17:36
If I introduce that alias does temporary table is created in tembdb in system tables ?
gruber
2010-10-29 10:25:17
@gruber - the alias is just needed because every table (or row source that resembles a table) in the FROM clause has to have a name. Whether result sets end up going into tempdb is entirely up to the optimizer, based on the size of the result set, and the type of operations performed (e.g. if you sort a large result set, it's likely to end up in tempdb)
Damien_The_Unbeliever
2010-10-29 10:32:42
+1
A:
If you want to filter the query based on some criteria then you could do this -
Select * from table_1 where table_1.col1 = <some value>
UNION
Select * from table_2 where table_2.col1 = <some value>
But, I would say if you want to filter result to find the common values then you can use joins instead
Select * from table_1 inner join table_2 on table_1.col1 = table_2.col1
Pavanred
2010-10-29 10:18:18