MySQL Return query result if condition is met, without repeating query

Question

Let's say I have something as basic as

SELECT IF( (1 + 2) >=0, (1 + 2), 0)
FROM DUAL

which will obviously return 3.

Is there a way to NOT repeat the query I want to return (here 1 + 2)?

To put in context, I want to return the number of tasks remaining to do for the current month (at least 4 must be done), but if more than 4 are already done, there is no need to do more so I want to return 0.

What I have done is

IF((4 - IFNULL((LONG QUERY RETURNING THE NUMBER OF TASKS DONE THIS MONTH FOR A PARTICULAR USER ),0)) >= 0,
(4 - IFNULL((LONG QUERY RETURNING THE NUMBER OF TASKS DONE THIS MONTH FOR A PARTICULAR USER ), 
0)

But is there a way to not repeat the query since it is long and I don't want the server to execute the same query twice?

Sylvain Leroux · Accepted Answer · 2013-08-08T14:04:43.507

4

Depending your request, you might want to use user-defined variables:

SELECT 1+2 INTO @tmp;
SELECT IF( @tmp >=0, @tmp, 0)

Or if you like one liners

SELECT IF( (@tmp := (1+2)) >=0, @tmp, 0)
--         ^             ^
--        don't forget those parenthesis !

EDIT: This works for "table select" statements too:

CREATE TABLE tbl AS SELECT 1 as a UNION SELECT 2;
SELECT SUM(a) FROM tbl INTO @tmp;
SELECT IF ( @tmp > 0, @tmp, 0);

If you look at http://sqlfiddle.com/#!2/37c33/1 for the execution plan of the above queries, you will see the second SELECT don't use the table.

BTW, please note that with your actual example, this could have been written:

SELECT MAX( (1+2), 0 )
--          ^^^^^ here your expression

... but I think this is not a property of your real query?

edited Aug 08 '13 at 14:04

answered Aug 08 '13 at 13:53

Sylvain Leroux

50,096
7
103
125

The thing is that this is part of a bigger query. It's one of the _select_expr_ of the SELECT statement. I don't think it would be possible... – JP P. Aug 08 '13 at 13:58
@JPP. You will probably have to show an example of *actual* query. But as far as I can tell, [according to the doc](http://dev.mysql.com/doc/refman/5.0/en/select.html), *select_expr* is allowed in `SELECT ... INTO` – Sylvain Leroux Aug 08 '13 at 14:01
-Neverming the first comment, I did'nt see your edit at time of writing - I didn't think of doing `MAX()` but now that you said it, it would actually work. But the method I should really use is the _one liner_ you proposed. I did't know I could do that, thank you! – JP P. Aug 08 '13 at 14:05

score 2 · Answer 2 · answered Aug 08 '13 at 13:54

2

You can do this:

SELECT IF( (@a:=(1 + 2)) >=0, @a, 0);

answered Aug 08 '13 at 13:54

Alma Do

37,009
9
76
105

score 1 · Answer 3 · edited May 23 '17 at 11:50

One statement solution

A way to solve this problem in MySQL is to save the output of the heavy query and reuse multiple times. Take a look here:

SELECT CASE 
  WHEN @Value - 4 > 0 
  THEN @Value ELSE 0 
  END
FROM (SELECT @Value := (SELECT 1)) Query

where «SELECT 1» should be replaced by your query.

OT

Another way to perform your query would require the support of CTE (Common Table Expression).

For what I know this feature is missing in MySQL (How to transform a MSSQL CTE query to MySQL?)

Just to give you a taste of its expressiveness in MSSQL, where CTE is available, you could write something like that:

with Query (value) 
as
(
    select 1
 )
select 
  case
    when (select value from Query) - 4 > 0
      then (select value from Query)
    else
      0
  end

Indeed CTE are more powerful especially when dealing with recursive relations.

MySQL Return query result if condition is met, without repeating query

3 Answers3