'Mysql query to get distinct row with column having null and duplicate values

So I have a weird situation in which I have a table like this :

mysql> select * from test;
+-------+------+
| name  | sal  |
+-------+------+
| agent | 1000 |
| agent | 2000 |
| NULL  | 3000 |
| NULL  | 4000 |
| smith | 5000 |
| smith | 6000 |
| neo   | 7000 |
+-------+------+

I want to return a data set which looks like this :

+-------+------+
| name  | sal  |
+-------+------+
| agent | 1000 |
| NULL  | 3000 |
| NULL  | 4000 |
| smith | 5000 |
| neo   | 7000 |
+-------+------+

Meaning I want to fetch unique rows wherever name is duplicated, but fetch all rows as it is when name is null or is not duplicated.

I have written this query below in order to achieve that and it works fine. But I want to optimize it.

select * 
from test 
where sal in (
                select sal from ( 
                        select min(sal) as sal 
                        from test 
                        group by name 
                    union 
                        select sal 
                        from test where name is null 
                ) t 
order by sal);

Queries for creating this sample data -

create table test (name text, sal int);
insert into test values ('agent',1000);
insert into test values ('agent',2000);
insert into test values (null,3000);
insert into test values (null,4000);
insert into test values ('smith',5000);
insert into test values ('smith',6000);
insert into test values ('neo',7000);

Can anyone help me with that? I know that we shouldn't use IN to fetch data because that will increase the query time a lot in production.

Any help is appreciated!



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source