with, ifnull, question about = and := in mysql

I'm writing a statement that only present the nth highest salary For example, get the second highest salary from the following table, the result is 200. +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ If I write: " declare M int; set M = N-1; with s as (select distinct Salary from Employee order by Salary desc limit M, 1) select ifnull(s, null) as getNthHighestSalary"

it reports s not exist, but if I write:

declare M int; set M = N-1;

select ifnull( (select distinct Salary from Employee order by Salary desc limit M, 1), null) as getNthHighestSalary

it will work. I understand "with as" creates temporary table, but I don't understand why ifnull() cannot find s.

Also, set M = N-1 and M:= N-1 have the same result, could anyone please explain to me that ":= or =" which is better?

https://stackoverflow.com/questions/66809636/with-ifnull-question-about-and-in-mysql March 26, 2021 at 09:03AM