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

Question

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.

Answer 1

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