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

This article will explain in detail about the efficiency of in and exist in sql. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

1. IN and EXISTS

1. Understanding

The execution process of IN

SELECT * FROM T1 WHERE X IN (SELECT Y FROM T2)

In fact, it can be understood as:

SELECT * FROM T1, (SELECT DISTINCT Y FROM T2) T2 WHERE T1.X = T2.Y

As can be seen here, IN needs to process the T2 table before associating it with T1.

The execution process of EXISTS

SELECT * FROM T1 WHEREEXISTS (SELECT

NULLFROM T2 WHEREY = X)-it can be understood as: for xin (select)

* fromt1) LOOP if (exists (selectnull)

From t2where y = x.x) THEN OUTPUTTHE RECORD endifend loop

As you can see here, EXISXTS will query T1 table first, and then LOOP will process T2 table

2. Conclusion

For 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 will be accessed first, and if it is IN, then the subquery will be 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.

Summing up the above discussion of IN/EXISTS, we can draw a basic general conclusion: IN is suitable for the case of large appearance and small inner table, and EXISTS is suitable for the case of small appearance and large inner table.

II. NOT IN and NOT EXISTS

1. Understanding

The execution process of NOT IN

SELECT * FROM T1 WHERE X NOT IN (SELECT Y FROM T2)

In fact, it can be understood as:

SELECT * FROM T1, (SELECT DISTINCT Y FROM T2) T2 WHERE T1.X! = T2.Y

The execution process of NOT EXISTS

SELECT.. ... FROMROLLUP R WHERE NOTEXISTS (SELECT'Found'

FROM TITLE TWHERE R.SOURCE_ID = T.TITLE_ID);-- can be understood as: for xin (select)

* fromrollup) loop if (

Not exists (that query)) then OUTPUT endif; end

Note: NOT EXISTS and NOT IN cannot completely replace each other, depending on the specific requirements. If the selected column can be empty, it cannot be replaced. For details, see in/exists and notin/not exists semantic discussion.

2. Conclusion

Notin only uses notin when the field after the select keyword in the subquery has a not null constraint or this implication. In addition, if the table in the main query is large, and the table in the subquery is small but there are many records, you should use notin and anti hashjoin. If there are fewer records in the main query table, more records in the sub-query table and indexes, you can use not exists. In addition, not in can also be used or external join + isnull. In general, not exists is recommended.

For example, SELECT.. .... FROMROLLUP R WHERE NOTEXISTS (SELECT'Found'

FROM TITLE TWHERE R.SOURCE_ID = T.TITLE_ID);-- changed to SELECT. .... FROMTITLE T, ROLLUP

R WHERE R.SOURCE_ID = T.TITLE_ID (+) ANDT.TITLE_ID

IS NULL;-- or SELECT.... .. FROMROLLUP R WHERE OURCE_IDNOT

IN (SELECTOURCE_ID

FROM TITLE TWHERE OURCE_ID

ISNOT NULL)

This is the end of this article on "what is the efficiency of in and exist in sql?". 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 out for more people to see.

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.