How do I write a conditional statement (IF) with MySQL?

Question

What is wrong in below simple query? I tried to google it but not found

IF 1 = 1 THEN
 SELECT 1;
ELSE
 SELECT 12;
END IF;

Answer 1

I believe that syntax is actually valid; you can find similar statements in the MySQL documentation.

However, it's considered a control flow statement, and those are only allowed in stored programs in MySQL. You don't state that this is inside a stored program, so I'll assume it's not. If it is, please edit your question to provide more detail.

If you're trying to do this outside a stored program, see Evan Carroll's answer.

Answer 2

What you want is a CASE expression which is standard-SQL method of implementing a conditional and supported by every major database,

SELECT CASE WHEN 1 = 1 THEN 1 ELSE 12 END;

Or you can use the totally silly and proprietary IF statement

SELECT IF( 1=1, 1, 12 );

Answer 3

After checking what seemed to be thousands answers on Stack Overflow I found a work-around for me:

let's imagine we store a condition in a boolean variable called @DEBUG.

SET @DEBUG=1;

Then, dependently on its value, we may execute SELECT query.

SELECT v FROM some_table, ( SELECT @DEBUG as DEBUG ) AS DBG WHERE DEBUG=1;

The good thing is, that this conditional SQL query may be executed outside of a procedure statement.

And if comparison to the standard SELECT IF() statement it can return more than one result.

Tested with MySQL 5.7

Answer 4

This simple query can't be run outside the stored routines as stated by chapter 13.6.5 of the manual. For the single queries IF() function should be used instead. CASE may be a bit confusing because the CASE statement and the CASE operator has exactly the same syntax, inspite of completely different nature.