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

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

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

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

 ------- ------ 
| name  | sal  |
 ------- ------ 
| john  | 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!

CodePudding user response：

You can try to use two queries with UNION ALL one is for name which value is null, another one writes MIN aggregate function by name with name isn't NULL.

Query #1

SELECT *
FROM (
  SELECT name,sal
  FROM test
  WHERE name IS NULL
  UNION ALL
  SELECT name,min(sal)
  FROM test
  WHERE name IS NOT NULL
  group by name
)t1
ORDER BY sal;

name	sal
agent	1000
	3000
	4000
smith	5000
neo	7000

View on DB Fiddle

Note

You can try to create an index on name column which might help you improve the query performance