Where Conditon with Union or Union ALL in SQL

Where Conditon with Union or Union ALL in SQL

This Post is about how to use where conditon with Union and Union All operator

I have a query which uses Union all.

Below two queries are working fine 

select Col1,Col2 from table1
Union all
select Col1,Col2 from table2

--or for union

select Col1,Col2 from table1
Union 
select Col1,Col2 from table2

Now if you want that from table 1 it will show only that data where col is not null and from column 2 you want to show data where col2 >10

for this you cannot write query like this

select Col1,Col2 from table1
Union all 
select Col1,Col2 from table2

where table1.col is not null and table2.col2>10

this will throw an error 
Msg 207, Level 16, State 1, Line 4

Invalid column name table1.col

 In order to apply a where clause on the result set to column which are not the part of select clause.

Below is the solution

--Note Column Col from table1 is not in select column list

select Col1,Col2 from table1

where col is not null

Union all 
select Col1,Col2 from table2

table2.col2>10

-- for union

select Col1,Col2 from table1

where col is not null

Union 
select Col1,Col2 from table2

table2.col2>10

In case you want to filter complete union query then put the  complete union query inside a CTE or Derived Table.

--Using Derived table

Select * from 
(select Col1,Col2 from table1
Union all 
selection Col1,Col2 from table2)x

where x.col is not null and x.col2>10

--Using CTE

with x as
(select Col1,Col2 from table1
Union all 
selection Col1,Col2 from table2

)

select * from x where x.col is not null and x.col2>10