CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012isnull
dotNET
dotNET

Reputation: 35470

ISNULL() returning NULL

Why does the following simple query return null when there are no matching rows (<Condition> is not met by any row)?

SELECT ISNULL(MyField, 0) FROM [MyTable] WHERE <Condition>

I have tried COALESCE() as well, with similar results. How can I return zero when there are no matching rows?

Upvotes: 2

Views: 3390

Answers (3)

Deepak Mishra
Deepak Mishra

Reputation: 3203

You cannot convert 0 rows to 1 row with a null value with any sql built-in fuction because that may cause mis-interpretation of data

But you can customize your result using the below logic (same as you do in .net).

If (select COUNT(MyField) FROM [MyTable] WHERE <Condition>)=0
select 0 as [MyField]
else
SELECT ISNULL(MyField, 0) as [MyField] FROM [MyTable] WHERE <Condition>

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

This will work, provided you expect condition to reduce the result set to either 0 or 1 row:

SELECT ISNULL((SELECT MyField FROM [MyTable] WHERE <Condition>),0)

That is, create an outer query with no FROM clause (which will therefore always generate exactly one row) and then use a subquery to obtain your 0 or 1 row of actual data.

Upvotes: 3

SanyTiger
SanyTiger

Reputation: 666

Use this:

SELECT ISNULL(COUNT(MyField), 0) FROM [MyTable] WHERE <Condition>

It'll return 0 if row is missing.

Upvotes: 0

Related Questions