SQL 里的 EXISTS与in、not exists与not in(国外英语资料).doc

SQL 里的 EXISTS与in、not exists与not in EXISTS in SQL, not EXISTS and not in? 2011-01-07 10:01:25 |? Classification: SQL |?? Label: |, small and medium size? To subscribe to The system calls for SQL optimization, optimized for less efficient SQL, and makes it more efficient, requiring that some of the in/not in SQL are modified to be exists/not exists ? The changes are as follows: In the SQL statement SELECT id, category_id, htmlfile, title, convert (varchar (20), begintime, 112) as pubtime FROM tab_oa_pub WHERE is_check = 1 and Category_id in (select id from tab_oa_pub_cate where no = 1) The order by begintime desc Modify the SQL statement for exists SELECT id, category_id, htmlfile, title, convert (varchar (20), begintime, 112) as pubtime FROM tab_oa_pub WHERE is_check = 1 and Exists (select id from tab_oa_pub_cate where tab_oa_pub. Category_id = convert (int, no) and no = 1) The order by begintime desc ? Is it true that exists is more efficient than in? ? ????? Lets talk about IN and EXISTS. ????? Select * from t1 where x in (select y from t2) ????? In fact, it can be understood as: ????? Select * ????? So from t1, we have a select distinct y from t2 ???? Where is it? ????? SQL optimization, if you have some experience, from the very nature of you can think of t2 never is a big table, because they need a full table of t2 sort only, if this sort of t2 much performance is unbearable. But t1 can be big. Why? The most popular understanding is that t1. X = t2. But this is not a good explanation. Imagine that if t1. X and t2. Y both have indexes, we know that the index is an ordered structure, so the best solution between t1 and t2 is a merge join. In addition, if t2. Y has indexes, the sorting performance of t2 is also greatly improved. ????? Select * from t1 where exists (select null from t2 where y = x) ????? It can be understood as: ????? For x in (select * from t1) ????? loop ????????? If (select null from t2 where y = x.x) ????????? then ??????????????? THE OUTPUT of