In SQL, How to output "NULL" instead of " There are no results to be displayed"-CodePudding

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.

CodePudding user response：

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
UNION ALL
SELECT NULL, NULL
WHERE 0 = ( SELECT COUNT(*)
            FROM sub 
            WHERE num = 2 );

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=31f5afb0e7e5dce9c2c128ccc49a6f42

CodePudding user response：

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.)