Sql query that not return null values in result

Question

I am really newbie with sqlserver.

I am querying some data from database and it is also returning null values with result.

My query is;

select amount, distributorid from paymants

Some distributors has null values in amount column.

Please help!

Thanks

Answer 1

You should use is null (or is not null) to filter null values.

select amount, distributorid 
from paymants
where amount is not null

If you need all records with null amount with another value (say, -1) you could use isnull or coalesce as below.

select coalesce(amount,-1) amount, distributorid 
from paymants

Or, if you need only amount null records, you could do;

select amount, distributorid 
from paymants
where amount is null 

Answer 2

If you want the rows that has NULL values, but that the value should be 0 instead you can write:

SELECT ISNULL(amount,0), distributorid FROM paymants

Link with info about ISNULL -> http://technet.microsoft.com/en-us/library/ms184325.aspx

and as pointed out in many other answers, if you don't want those rows to be returned at all, you can simply write:

select amount, distributorid 
from paymants
where amount is not null