Replace null with zero in sql query

I have an sql query that could potentially return null values, in the event of this I want the query to return ‘0’. Here is the query

   SELECT (select count(goal) from fixtures where goal='1' and fixture='$fixture') as goalCountHome
from fixtures where fixture='$fixture'LIMIT 1

Any help much appreciated!

Answer

This query:

SELECT (select count(goal) from fixtures where goal='1' and fixture='$fixture') as goalCountHome
FROM fixtures 
WHERE fixture = '$fixture'
LIMIT 1

cannot return NULL values. The subquery is an aggregation query with no GROUP BY, so it always returns one row. That row will contain a result from COUNT(). COUNT() itself can never return a NULL value. If there are no rows, then the value will be zero.

The outer query might return no rows but that is different from NULL values.

Of course, this query is way overcomplicated, and should simply be:

SELECT COUNT(*) as goalCountHome
FROM fixtures 
WHERE fixture = ? AND   -- pass this in as a parameter 
      goal = 1 ;        -- it looks like a number so I assume it is

Note that you should be passing parameters in using proper parameters rather than munging query strings.