CODEWITHSUNDEEP
sqlsql-serverzerounion-all
Eduardo
Eduardo

Reputation: 21048

SQL union all not includes zero values

I am using union all to unite several queries, but union all is not inserting an empty row for those zero values. I took a look in the site and according to this question, it should include them:

mysql union with NULL fields

These are my queries:

select [SMTH] from [H].[D].[T]
where Date = (SELECT MAX(DATE) from [H].[D].[T] where Data = 1 and SMTH > 0 )  
union all
select [SMTH] from [H].[D].[T]
where Date = (SELECT MAX(DATE) from [H].[D].[T] where Data = 2 and SMTH > 0 )  
union all
select [SMTH] from [H].[D].[T]
where Date = (SELECT MAX(DATE) from [H].[D].[T] where Data = 3 and SMTH > 0 )  
union all
select [SMTH] from [H].[D].[T]
where Date = (SELECT MAX(DATE) from [H].[D].[T] where Data = 4 and SMTH > 0 )

Result:

10068
3967
895

Four independent queries:

select [SMTH] from [H].[D].[T]
where Date = (SELECT MAX(DATE) from [H].[D].[T] where Data = 1 and SMTH > 0 )  

select [SMTH] from [H].[D].[T]
where Date = (SELECT MAX(DATE) from [H].[D].[T] where Data = 2 and SMTH > 0 )  

select [SMTH] from [H].[D].[T]
where Date = (SELECT MAX(DATE) from [H].[D].[T] where Data = 3 and SMTH > 0 )  

select [SMTH] from [H].[D].[T]
where Date = (SELECT MAX(DATE) from [H].[D].[T] where Data = 4 and SMTH > 0 )

Result:

10068
3967
0
895

So basically since I think that the answer from the previous question is right, what am I doing wrong?.

Thank you!

Upvotes: 0

Views: 2817

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

The third query returns 0. Why do you think that is null? Any way this query is cleaner than the union:

If the table has a primary key (id in this query):

select SMTH, Data, [date]
from 
    H.D.T
    inner join (
        select id, Data, max([date]) as [date]
        from H.D.T
        where Data in (1, 2, 3, 4) and SMTH > 0
        group by id, Data
    ) s on s.id = H.D.T.id
order by Data

Upvotes: 0

lilalinux
lilalinux

Reputation: 3031

Which column is null? Are you sure, the nulls aren't removed due to your where clause? Remember that operations with null always result in null (except testing with IS NULL).

Upvotes: 2

Related Questions