CODEWITHSUNDEEP
mysql
user1792210
user1792210

Reputation: 155

Setting variables in SELECT statement mysql

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.

Upvotes: 0

Views: 875

Answers (1)

PM 77-1
PM 77-1

Reputation: 13334

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.

Upvotes: 2

Related Questions