Setting variables in SELECT statement mysql

Question

Hi I'd like to know if it's OK to assume that the SELECT statement selects the fields from left to right to make sure that it is safe to construct a statement as the following (it wouldn't be nice to try to use a variable that has't been set):

SELECT @variable := IFNULL(var, 0), value + @variable, somevalue, value2*@variable, ..., @variable/whatever...

FROM table1 LEFT JOIN table2 LEFT JOIN table3

The reason I'm asking the question is because it is possible that var equals null in some of the tuples due the use of the LEFT JOINS (let's suppose that var comes from table 3), and I need to use the variable several times to make derivative fields as you can see, so I don't want to use IFNULL(var, 0) every time.

I don't understand the reason for the vote down, I'm newbie to sql but I don't consider the question to be irrelevant or silly!. — user1792210
– user1792210, Commented Mar 2, 2015 at 6:11

PM 77-1 · Accepted Answer · 2015-03-02 06:15:02Z

2

From the docs (with my emphasis):

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. For example, to increment a variable, this is okay:

SET @a = @a + 1;

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:

SELECT @a, @a:=@a+1, ...;

However, the order of evaluation for expressions involving user variables is undefined.

So, if we go by the book, the answer to your question is NO.

answered Mar 2, 2015 at 6:15

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1792210 Over a year ago

Ok, I have read a book about mysql but there wasn't any mention of what you just explained. Thanks.

PM 77-1 Over a year ago

Just to be clear. There are plenty of examples that use multiple in-line assignments in the same SELECT statement quite successfully (including here, on SO). But it all is in the might category.

Collectives™ on Stack Overflow

Setting variables in SELECT statement mysql

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related