MySQL IF...THEN syntax error

Question

I've been trying to work with the IF statement in MySQL (v5.6.13) without any success. I've eventually pared my code back to the simple example below, which still doesn't work:

IF 10>1 THEN
    SELECT 10;
END IF;

When I run this, it gives me the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near 'IF 10>1 THEN
    SELECT 10' at line 1

I did wonder whether it was something to do with the MySQL client I'm using (Sequel Pro), but I get exactly the same message running from the command line.

Am I making some kind of ridiculously simple mistake that I just can't see? Or does anyone have any suggestions as to what might be causing the error? Any help would be gratefully received!

You can only use if in program blocks, such as stored procedures, functions, and triggers. It is not recognized in other contexts. — Gordon Linoff
– Gordon Linoff, Commented Dec 29, 2014 at 13:05
there is, however, an inline if statement. Good example here: timmurphy.org/2009/08/13/inline-if-and-case-statements-in-mysql — Russell Uhl
– Russell Uhl, Commented Dec 29, 2014 at 13:44
Thanks @GordonLinoff - very helpful! Reading the docs back now this point is sort of understandable there, but nowhere is it particularly explicit (well, IMO!). — HaydnW
– HaydnW, Commented Dec 29, 2014 at 14:54

Community · Accepted Answer · 2017-05-23 11:59:23Z

As indicated in @Gordon Linoff's comment, MySQL's IF statement is only valid within stored programs (i.e. code that is stored within and then invoked by MySQL in response to particular conditions); that is to say, it cannot be used in the manner that you are attempting.

However, one rarely needs to use IF in SQL.

This may seem strange to people who are familiar with procedural languages (in which one's code describes the algorithm and process steps to be executed—thus control-flow constructs like "if…then…else" are invaluable), but SQL is a declarative language (in which one's code describes the desired result and not how that result should be achieved—thus attempting to use control-flow constructs almost certainly arises from a misunderstanding of the language).

By way of example, in a procedural language one might tell the computer to perform a comparison, inspect the result and then output a value if the result meets certain criteria:

for each x in Set
  if x < 10 then
    output x

By contrast, in a declarative language one just tells the computer what result one wants:

output x from Set if x < 10

Or, in SQL:

SELECT column_name FROM table_name WHERE column_name < 10

Collectives™ on Stack Overflow

MySQL IF...THEN syntax error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related