4

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.

Improve this question
9
  • You mean you know you have a row where q1, q2, and q3 are all NULL while date and brand satisify your condition, and you don't get that row? Commented Dec 8, 2010 at 20:53
  • What is the datatype of the q variables? Commented Dec 8, 2010 at 20:55
  • Needing to return a record with all-NULL fields instead of an empty recordset strikes me as a peculiar use case - if you describe what you're doing with the result, you might get more-informative answers. Commented Dec 8, 2010 at 20:55
  • @djacobson Plenty of systems might have optional fields that are decently likely to have NULL values while containing other useful data. I've run queries like this and they've worked fine. Commented Dec 8, 2010 at 20:57
  • @ceejayoz I understand; my comment applies if the OP is in fact asking for a way to return a record with NULL fields instead of an empty recordset in the case where no record matches the WHERE clause. If that's not what he's asking, the current answers have it covered... Commented Dec 8, 2010 at 20:59

6 Answers 6

Reset to default
4

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

+1 I believe you need to specify a column alias for that inner query, like SELECT NULL AS col1 - at least, that's the case on SQL Server - but otherwise this approach appears to work, so I'm not sure why it was downvoted.
@djacobson: I don't think it's necessary in MySQL - I have tested it locally and it works fine. I can add it anyway for portability reasons.
Sounds good. And, as evident by the green checkmark, the circle is now complete. :)
PERFECT!!!! Thanks Mark - that has saved me HOURS AND HOURS of re-coding the application. Really appreciate it. Thanks again. I've used the DESC LIMIT option by the way :)
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?

Improve this answer

2 Comments

Inherited code - it would save major messing around with the application code - but if it can't be done I'll have to get my hands dirty!!!
@Homer_J It can be done, check @Mark Byers's answer. It just can't be done in a way which is not... kinda wacky. :)
2

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;
Improve this answer

Comments

1

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.

Improve this answer

2 Comments

Hmm, I fear that might be the case. The database doesn't have a record but I need it to return NULL's instead.
Why do you need it to return this? Why are three empty values better than an empty result set? Your application should be able to handle either case.
1

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'

Improve this answer

Comments

0

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.

Improve this answer

4 Comments

He's saying there is one row that matches the WHERE clause, but the selected columns are all equal to null. I would have thought this would return a single row of all nulls, but I guess not.
It will return a single row of all nulls. He's made a mistake somewhere.
Just tested it myself and you're right. Something strange is going on.
Yeah. I'm wondering if dates are genuinely stored in that format, or if there's a space at the end of 'XYZ' in the db, or something like that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.