Why does moving a subquery from FROM to SELECT result in NULL?

user_8629729 0
Viewed 94

I'm trying to understand a NULL-related behavior in MySQL. The behavior came up when I was trying out the problem below on LeetCode:

Write a SQL query to get the second highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

I am new to SQL and am still learning the ins and outs of working with NULL values. I tried the following query to see if it would return NULL when there was no Second Highest Salary:

SELECT e.Salary as SecondHighestSalary
FROM ( 
    SELECT DISTINCT Salary from Employee ORDER BY Salary DESC LIMIT 1, 1
) as e

This query returns nothing in the case where there is no Second Highest Salary. (I think it's nothing; in any case, it does not return NULL or the empty string.)

I eventually came up with a more convoluted query that answered the question. However, when looking in the discussion section to compare against other people's solutions, I found this:

select (
  select distinct Salary from Employee order by Salary Desc limit 1 offset 1
)as SecondHighestSalary

(Fiddle.)

Unlike my first attempt, this query does return NULL in the case where the SecondHighestSalary doesn't exist!

Does anyone know why moving the same subquery from the FROM clause to SELECT changes the return behavior in this case?

mysql sql
1 Answers

In the second query, you have a subquery that returns a value in the SELECT. This is called a scalar subquery.

In general, a scalar subquery can return exactly one column and zero or one rows -- otherwise you get an error (in some contexts in some databases, multiple columns are allowed, but that is not relevant here).

Overall, a select with no from returns exactly one row, no matter what. A scalar subquery that returns no rows is treated as a NULL value in the outer query. That is why you get one row with NULL, rather than zero rows.

user_11440350
Related