Database query optimization: subquery optimization

1. Case

Take all employees who are not the head of the company and group them by age!

select age as 'age', count(*) as 'number of people' from t_emp where id not in 
(select ceo from t_dept where ceo is not null) group by age; 

How to optimize?

①Solve the full table scan of the dept table and create an index for the ceo field:

At this time, query again:

② Further optimization, replace not in.

The above SQL can be replaced with:

select age as 'age',count(*) as 'number of people' from emp e left join dept d on e.id=d.ceo where d.id is null group by age; 

Conclusion: When judging the range, try not to use not in and not exists, use left join on xxx is null instead.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links