Using variables in SQL SELECT statements and its evaluation

Question

SELECT (@row:=@row+1) AS ROW, ID  
FROM table1 ,(SELECT @row := 0) r   
order by ID desc

How does SQl evaluate this? Strangely, there seems to be little to no literature on this sort of this with variables in the SELECT statement.

How can SELECT select row = 0? I thought select had to select an entire column
what is the r here?
How does the SELECT @row:=@row+1 know how to iterate through the rows?
Its not clear to me how the ID is set here. The @row surely correspond somehow to the ROW, but ID seems to be just hanging on
Why does the FROM need to take from more than just the table 1?

example taken from here stackoverflow.com/questions/12707646/… — Trajan
– Trajan, Commented Apr 17, 2021 at 17:45
MySQL has lots of weird and funny (and downright awful) extensions to ISO SQL. Support for incrementing a variable inside a SELECT statement is one of them. Fortunately you don't need to use this terrible technique anymore because now MySQL supports the ISO SQL ROW_NUMBER() function now: dev.mysql.com/doc/refman/8.0/en/… — Dai
– Dai, Commented Apr 17, 2021 at 17:47

Gordon Linoff · Accepted Answer · 2021-04-17 18:28:02Z

2

There is very important "literature" on this:

It is also possible to assign a value to a user variable in statements other than SET. (This functionality is deprecated in MySQL 8.0 and subject to removal in a subsequent release.)

In other words, this code is going to stop working at some point in MySQL.

The correct way to do what you want is to use row_number():

SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) AS ROW, ID  
FROM table1 
ORDER BY ID DESC;

The only reason to learn about variable assignment in MySQL SELECT statements is historical curiosity -- or if you are stuck maintaining a system that has not yet been migrated to MySQL 8+.

answered Apr 17, 2021 at 18:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

eshirvana · Accepted Answer · 2021-04-17 18:17:17Z

1

(SELECT @row := 0) r , is translated as a table with 1 column (column with no name) and you are giving the table an alias name "r"
you are doing join between table1 and r table, so if you execute :

SELECT  *  
FROM table1 ,(SELECT @row := 0) r   
order by ID desc

you will see all the columns from table1 plus a new column ( with no name) from table which is always 0

however in your query you are showing a variable winch each time for each is calculated , but also each time it executes you assign a new value to your variable which is itself + 1 @row:=@row+1
you are sorting it by Id , so how you are showing the final result , if you change your order by ,it will find out the difference

answered Apr 17, 2021 at 18:17

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Collectives™ on Stack Overflow

Using variables in SQL SELECT statements and its evaluation

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related