CODEWITHSUNDEEP
mysqlnull
Homer_J
Homer_J

Reputation: 3323

MySQL NULL 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.

Upvotes: 4

Views: 1116

Answers (6)

Mark Byers
Mark Byers

Reputation: 838246

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

Upvotes: 4

Riedsio
Riedsio

Reputation: 9926

If you're only expecting 1 or 0 rows, this should work

SELECT q1,q2,q3 FROM
    (
    SELECT q1, q2, q3 FROM tresults WHERE date = 'NOV2010' AND brand = 'XYZ'
    UNION
    SELECT NULL AS q1, NULL AS q2 NULL AS q3 
    ) sq
ORDER BY q1 DESC LIMIT 1;

Upvotes: 2

Dan J
Dan J

Reputation: 16708

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?

Upvotes: 2

Roman Zenka
Roman Zenka

Reputation: 3604

What you expect is indeed what should happen. Check once more that your database really contains an entry for NOV2010 and XYZ (maybe the O in NOV is accidentally a 0, or similar typo?)

What happens when you do this?

SELECT * FROM tresults WHERE date = 'NOV2010' AND brand = 'XYZ'

Upvotes: 1

ceejayoz
ceejayoz

Reputation: 180024

MySQL already works that way. If there are zero rows, no rows matched your WHERE clause.

edit: Per your update, no, I don't think you can do that. Your application logic is where that should be handled.

Upvotes: 0

cdhowie
cdhowie

Reputation: 169018

Assuming that your question is accurate, and you actually have a row with three NULLs, I would expect MySQL to return it. If it doesn't, you can force the selection of a non-null value:

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

If your DB does not, however, have a record in tresults that satisfies the criteria, you are SOL -- there is no record in the DB, so MySQL is not going to return anything to you.

Upvotes: 1

Related Questions