Filter union result

Question

I'm making select with union. select * from table_1 union select * from table_2 ...

Is it possible to filter query result by column values?

Solution 1

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.

Solution 2

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

Solution 3

A simple to read solution is to use a CTE (common table expression). This takes the form:

WITH foobar AS (
    SELECT foo, bar FROM table_1
    UNION
    SELECT foo, bar FROM table_2
)

Then you can refer to the CTE in subsequent queries by name, as if it were a normal table:

SELECT foo,bar FROM foobar WHERE foo = 'value'

CTEs are quite powerful, I recommend further reading here

One tip that you will not find in that MS article is; if you require more than one CTE put a comma between the expression statements. eg:

WITH foo AS (
    SELECT thing FROM place WHERE field = 'Value'
),
bar AS (
    SELECT otherthing FROM otherplace WHERE otherfield = 'Other Value'
)

Comments

[gruber]

If I introduce that alias does temporary table is created in tembdb in system tables ?

[Damien_The_Unbeliever]

@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)

[Admin]

the tip is probably in this section... -- Syntax for SQL Server [ WITH <common_table_expression> [ ,...n ] ] <common_table_expression>::= expression_name [ ( column_name [ ,...n ] ) ] AS ( CTE_query_definition ) but I don't know how to decipher these things... any one know where I can look up the syntax of the syntax definition?

[Altair]

It's this bit: [ ,...n ]