MySQL NULL question

Question

THANKS EVERYONE - GREAT RESULT!

Hi all - I should have explained in further detail - there is NO row that matches that crieria and therefore the current result of the query is correct (i.e zero rows) but is it possible to force MySQL to return this instead?

q1     q2     q3
NULL   NULL   NULL

I suspect not from the responses below!

Cheers,

H.

Hi all,

This maybe a really, really simple question but I'm stumped!

I have the following query:

SELECT q1, q2, q3 FROM tresults WHERE date = 'NOV2010' AND brand = 'XYZ'

Now, if q1, q2 and q3 are empty for NOV2010 and XYZ the result of the query is:

MySQL returned an empty result set (i.e. zero rows).

What I need however, is to force MySQL to return NULL instead, for example the query would result in:

q1     q2     q3
NULL   NULL   NULL

I'm sure it is dead simple but, as I say, I'm stumped.

Thanks,

Homer.

Answer 1

I don't think it is simple - it seems a very unusual thing to want to be able to do. If I had to do this I think I would use a LEFT JOIN:

SELECT q1, q2, q3
FROM (SELECT NULL AS foo) T1
LEFT JOIN tresults ON date = 'NOV2010' AND brand = 'XYZ'

Here's another approach that will work if q1 is not nullable and you only need one row:

SELECT q1, q2, q3 FROM tresults WHERE date = 'NOV2010' AND brand = 'XYZ'
UNION ALL
SELECT NULL, NULL, NULL
ORDER BY q1 DESC
LIMIT 1

Answer 2

As others have pointed out, returning a "NULL" record rather than an empty resultset when no records match your query is somewhat unusual, and I don't know of a terribly-elegant way to support it. One option is to UNION your recordset as below:

SELECT q1, q2, q3 FROM tresults WHERE date = 'NOV2010' AND brand = 'XYZ'
UNION
SELECT NULL AS q1, NULL AS q2, NULL AS q3

This ensures you will always get a resultset, but it also means the "NULL" record will be appended to your resultset even when the first query does return records...

That said, as per @ceejayoz's answer, this would be better handled in your application logic. Would you mind elaborating on why you require this result?