Shulou(Shulou.com)05/31 Report--

This article is to share with you about the use of in and exists in sql, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

The difference between in and exists: if the subquery results in fewer result set records, in should be used when the table in the main query is larger and has an index, whereas if the outer main query record is less, the table in the subquery is large, and exists is used when there is an index. In fact, our distinction between in and exists is mainly caused by the change in the driving order (which is the key to the change in performance). If it is exists, then the outer layer table is the driven table and is accessed first, and if it is IN, then the subquery is executed first, so we will aim to drive the quick return of the table, then we will take into account the relationship between index and result set. In addition, NULL is not processed in IN.

In connects the outer table with the inner table as hash, while exists uses the external table as a loop loop, and each loop loop queries the inner table. It has always been inaccurate to think that exists is more efficient than in.

Not in and not exists

If the query statement uses not in, then both the inside and outside of the table are scanned without using the index, while the subquery of not extsts can still use the index on the table. So no matter which watch is big, it is faster to use not exists than not in.

The above is the use of in and exists in sql. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.