Geek Answers Handbook

MySQL - query to return NULL

I have the following code:

SELECT q25, ( ( AVG( q1 ) + AVG( q2 ) + AVG( q3 ) ) /3 ) AS Overall FROM t_results WHERE brand = 'XYZ' AND DATE = 'MAY2012' GROUP BY q25 ORDER BY Overall DESC LIMIT 1

If there is no data found in the request, phpmyadmin returns the following message (which is quite correct):

 MySQL returned an empty result set (ie zero rows). ( Query took 0.0178 sec )

However, I would like to return a NULL value, is this possible? I understand that this may not be the best practice, but I work with legacy code, and it can be a simplified and quick way to solve.

Thanks, as always

N.

+4
source share
3 answers

Create a table with exactly one row. Then you can use the left join to achieve the desired NULL result.

 CREATE TABLE dummy (d TINYINT NOT NULL); INSERT INTO dummy SET d = 1; SELECT q25, ( ( AVG( q1 ) + AVG( q2 ) + AVG( q3 ) ) /3 ) AS Overall FROM dummy LEFT JOIN t_results ON brand = 'XYZ' AND DATE = 'MAY2012' GROUP BY q25 ORDER BY Overall DESC LIMIT 1

You can also replace the dummy table with a subquery:

 SELECT q25, ( ( AVG( q1 ) + AVG( q2 ) + AVG( q3 ) ) /3 ) AS Overall FROM (SELECT 1) AS dummy LEFT JOIN t_results ON brand = 'XYZ' AND DATE = 'MAY2012' GROUP BY q25 ORDER BY Overall DESC LIMIT 1

Tested with sqlfiddle , where you can also experiment with alternatives.

Result selection conditions that were previously in the WHERE should now be included in the ON clause. Otherwise, the left join will create NULL strings that will be deleted using WHERE instead of generating a single NULL string if no matching string is found. If there were no WHERE clauses in the original query, ON 1 can be used to express any row matches .

+3
source

You can use UNION in combination with LIMIT to deliver NULL values:

 (SELECT q25, (AVG(q1) + AVG(q2) + AVG(q3))/3 AS Overall FROM t_results WHERE brand = 'XYZ' AND DATE = 'MAY2012' GROUP BY q25 ORDER BY Overall DESC LIMIT 1 ) UNION ALL (SELECT NULL, NULL) LIMIT 1;

This only works when you know that the first query will never return more than one result. What is the case here, so this may be the best solution for you, but the approach given in my other answer is more general.

There is a fiddle for this.

+2
source

The coalesce() function can be used to return the first non-empty value from among comma-separated columns or rows. Values ​​/ columns are evaluated left to right, so if you want to put a row in non-zero arguments, make sure you put them to the right of the columns you are testing.

 select coalesce( ( SELECT q25 FROM t_results WHERE brand = 'XYZ' AND DATE = 'MAY2012' GROUP BY q25 LIMIT 1 ), 'null') as q25, coalesce( ( SELECT ((AVG( q1 ) + AVG( q2 ) + AVG( q3 ) ) /3 ) AS Overall FROM t_results WHERE brand = 'XYZ' AND DATE = 'MAY2012' LIMIT 1 ), 'null') as Overall from t_results group by 1, 2;

If you don't have data that matches your where clause, this will return null, null as a string.

0
source

More articles:


All Articles