In SQL, How to output "NULL" instead of " There are no results to be displayed" when there's no value to be exported

user_11207179 0
Viewed 199

Using MySQL v8.0 right now.

The question is:

Write an SQL query to report the id and the salary of the second highest salary from the Employee table. If there is no second highest salary, the query should report null.

My dummy data is:

Create table If Not Exists Employee (id int, salary int);
insert into Employee (id, salary) values 
(1, 100);

My ideal output is like this:

+------+--------+
|  id  | salary |
+------+--------+
| NULL |  NULL  |
+------+--------+

I used DENSE_RANK as a more straightforward way for me to solve this question:

WITH sub AS (SELECT id,
       salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS num
       FROM Employee )
SELECT id, salary
FROM sub
WHERE num = 2

But I have a problem exporting NULL when there's no second highest salary. I tried IFNULL, but it didn't work. I guess it's because the output is not actually null but just empty.

Thank you in advance.

isnull mysql database
2 Answers
user_101387340

Just making your query a subquery and left joining from a single-row producing subquery seems to me the simplest approach:

select id, salary
from (select null) at_least_one_row
left join (
    select id, salary
    from (
        select id, salary, dense_rank() over (order by salary desc) as num
        from Employee
    ) ranked_employees
    where num = 2
) second_highest_salary on true

(I usually prefer a subquery to a cte that's only used once; I find that obfuscatory.)

ysth_173890
Related