Clash Royale CLAN TAG#URR8PPP

How to handle multiple select statment with union all

I have to get the results by using multiple SQL statements with union all, this is like:

`select coloumn1,coloumn2 from table1 where coloumn4 ='value1' union all
select coloumn1,coloumn2 from table1 where coloumn4 ='value2' union all
select coloumn1,coloumn2 from table1 where coloumn4 ='value3' union all
select coloumn1,coloumn2 from table1 where coloumn4 ='value4' union all
select coloumn1,coloumn2 from table1 where coloumn4 ='value5'`


for above, i will get only two results:

Because no results for another 2 select queries. but i need to record null values or no values in resultant table like:
coloumn1 coloumn2

How can get these kind of results??
whether i can handle nulls by using if statement in multiple sql statements.

3 Answers
3

I would use a description column to answer this type of code

select 'Value1' AS Value ,
coloumn1,
coloumn2

from table1

where coloumn4 ='value1'

union all


select 'Value2' AS Value ,
coloumn1,
coloumn2

from table1

where coloumn4 ='value2'

union all

select 'Value3' AS Value ,
coloumn1,
coloumn2

from table1

where coloumn4 ='value3'

union all

select 'Value4' AS Value ,
coloumn1,
coloumn2

from table1

where coloumn4 ='value4'

union all

select 'Value5' AS Value ,
coloumn1,
coloumn2

from table1

where coloumn4 ='value5'


Create a table variable for the values which you want to search. And then use a LEFT JOIN with the other table.

LEFT JOIN

Query

declare @t as table(
[column4] varchar(100)
);

insert into @t
select 'value1' union all
select 'value2' union all
select 'value3' union all
select 'value4' union all
select 'value5';

select [t2].[column1], [t2].[column2]
from @t as t1
left join [table1] as [t2
on t1.[column4] = [t2].[column4];


Use Isnull or coalesce

select isnull(coloumn1,'Null'),isnull(coloumn2,'Null') from table1 where coloumn4 ='value1'
union all
select isnull(coloumn1,'Null'),isnull(coloumn2,'Null') from table1 where coloumn4 ='value2'
union all
select isnull(coloumn1,'Null'),isnull(coloumn2,'Null') from table1 where coloumn4 ='value3'
union all
select isnull(coloumn1,'Null'),isnull(coloumn2,'Null') from table1 where coloumn4 ='value4'
union all
select isnull(coloumn1,'Null'),isnull(coloumn2,'Null') from table1 where coloumn4 ='value5'


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.