CODEWITHSUNDEEP
mysql
Searene
Searene

Reputation: 27654

How to convert empty to null in mysql

I have a table called Employee, it's empty, here is the information about the columns:

mysql> SHOW COLUMNS FROM Employee;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| Id     | int(11) | YES  |     | NULL    |       |
| Salary | int(11) | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

I tried to display the highest salary, if there's no highest salary(which is the case given that the table is empty for now), return null:

mysql> SELECT if (min(Salary) = max(Salary), null, min(Salary)) FROM Employee ORDER BY Salary;
+---------------------------------------------------+
| if (min(Salary) = max(Salary), null, min(Salary)) |
+---------------------------------------------------+
|                                              NULL |
+---------------------------------------------------+
1 row in set (0.00 sec)

Great, it worked. Then I tried to display the second highest salary, if there's no second highest salary(which is the case given that the table is empty for now), return null:

mysql> SELECT if (min(Salary) = max(Salary), null, min(Salary)) FROM Employee ORDER BY Salary DESC LIMIT 1,1;
Empty set (0.00 sec)

It returned Empty set instead of NULL, why? How to display NULL in this case?

Upvotes: 0

Views: 320

Answers (2)

Brendan Rollinson
Brendan Rollinson

Reputation: 204

It looks like your issue is in the limit clause.

Try using a LIMIT 0, 1 instead. MySQL's limits are 0-indexed, meaning that your query is currently trying to obtain the second record, which doesn't exist.

Try:

SELECT
    IF (MIN(Salary) = MAX(Salary),
    null,
    MIN(Salary))
FROM Employee
ORDER BY Salary DESC
LIMIT 0,1;

Upvotes: 0

KaeL
KaeL

Reputation: 3659

My guess is that the LIMIT works after your AGGREGATE functions, you will have to use subquery. Can you try the following query:

SELECT if (min(Salary) = max(Salary), null, min(Salary)) 
    FROM (
        SELECT Salary
            FROM Employee 
            ORDER BY Salary DESC 
            LIMIT 1,1;
    ) T

Upvotes: 1

Related Questions