MySQL null question

Question

THANKS EVERYONE - GREAT RESULT!

Hi everyone - I should have explained in more detail - there is no line that matches this crieria, and therefore the current query result is correct (i.e. zero lines), but is it possible to get MySQL to return this instead?

q1 q2 q3 NULL NULL NULL 

I suspect not one of the answers below!

Greetings

N.

Hello to all,

It may be a really, very simple question, but I'm at a standstill!

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:

 MySQL returned an empty result set (ie zero rows). 

However, I need to force MySQL to return NULL instead, for example, a query will result in:

 q1 q2 q3 NULL NULL NULL 

I am sure that he is dead, but, as I said, I'm at a standstill.

Thanks,

Homer.

Answer 1

I don’t think it’s easy - it seems very unusual that you need to be able to do it. If I had to do this, I would have thought that I would use LEFT JOIN:

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

Here is another approach that will work if q1 is not NULL and you only need one line:

 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 result set when the records do not match your query, is somewhat unusual, and I don’t know about the terribly elegant way to support it. One option is UNION your record set, as shown 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 that you always get a result set, but it also means that the "NULL" record will be added to your result set, even if the first query returns records ...

However, according to @ceejayoz's answer, this will be better handled in your application logic. Could you explain why you need this result?

Answer 3

If you expect only 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; 

Answer 4

Assuming your question is correct, and in fact you have a string with three NULLs, I would expect MySQL to return it. If this is not the case, you can force a non-zero value to be selected:

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

However, if your database does not have a record in tresults that meets the criteria, you are SOL - there is no record in the database, so MySQL will not return anything to you.

Answer 5

What you expect really has to happen. Check again that your database really contains an entry for NOV2010 and XYZ (maybe O in NOV is accidentally 0 or a similar typo?)

What happens when you do this?

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

Answer 6

MySQL already works that way. If there are null strings, no strings match your WHERE clause.

edit: For your update, no, I don’t think you can do it. The logic of your application is where it should be handled.