Example Analysis of Sub-query Optimization of Database query Optimization

This article will explain in detail the example analysis of sub-query optimization of database query optimization. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

1. Case

Take all the employees who are not in charge and group them according to age!

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

How to optimize?

① solves the full table scan of the dept table and establishes the index of the ceo field:

At this point, query again:

② is further optimized to replace not in.

The above SQL can be replaced with:

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

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

This is the end of the article on "sample Analysis of Sub-query Optimization of Database query Optimization". I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, please share it for more people to see.