sql與in相對

⑴ sql優化——IN和EXISTS誰的效率更高

在SQL編程中，IN和EXISTS是常被使用的子查詢方式，它們的功能相似但效率存在差異。通過對比不同場景下的測試，可以發現使用哪一種更為高效。

在測試1中，我們使用了子查詢`select flag from B where B_id<100`，結果集為99條。在這個場景下，IN的效率高於EXISTS。IN執行計劃中，首先執行A表的查詢，並利用索引優化，然後執行B表子查詢，同樣使用索引。而EXISTS的執行計劃則先取A表所有記錄，然後逐行關聯B表子查詢，使用Block Nested Loop優化。

測試2中，子查詢`select flag from B where B_id>100`，結果集為299899條。這次EXISTS的效率比IN高。兩者在索引使用上與第一次測試一致，但當子查詢結果集較大而外部表較小時，EXISTS的Block Nested Loop開始發揮優勢，查詢效率優於IN。

綜上所述，並不能斷言IN或EXISTS的效率更高，具體情況需要具體分析。IN的執行原理是先查詢內表得到結果集，再與外表匹配，不管子查詢是否有數據，都會進行全部匹配，這在內表數據量較大時效率較低。而EXISTS則是先對外表進行循環查詢，只在匹配到數據時返回結果，適用於子查詢數據量較大而外表數據量較小時。

結論是，子查詢結果集較大時使用EXISTS，而結果集較小時使用IN可能更有效。對於更詳細的信息和深入理解，可以參考墨天輪原文鏈接。

⑵ sql exist和in的區別及查詢效率比較

SQL查詢中in和exists的區別分析

select * from A where id in (select id from B);

select * from A where exists (select 1 from B where A.id=B.id);

對於以上兩種情況，in是在內存里遍歷比較，而exists需要查詢資料庫，所以當B表數據量較大時，exists效率優於in。

1、IN()語句內部工作原理

IN()只執行一次，它查出B表中的所有id欄位並緩存起來。之後，檢查A表的id是否與B表中的id相等，如果相等則將A表的記錄加入結果集中，直到遍歷完A表的所有記錄。
它的查詢過程類似於以下過程：

List resultSet={};

Array A=(select * from A);

Array B=(select id from B);for(int i=0;i<A.length;i++)

{

for(int j=0;j<B.length;j++) {

if(A[i].id==B[j].id) {
resultSet.add(A[i]); break;
}
}
}return resultSet;

可以看出，當B表數據較大時不適合使用in()，因為它會B表數據全部遍歷一次

• 例1：A表有10000條記錄，B表有1000000條記錄，那麼最多有可能遍歷10000*1000000次，效率很差。

• 例2：A表有10000條記錄，B表有100條記錄，那麼最多有可能遍歷10000*100次，遍歷次數大大減少，效率大大提升。

結論：IN()適合B表比A表數據小的情況

2、EXISTS()語句內部工作原理

exists()會執行A.length次，它並不緩存exists()結果集，因為exists()結果集的內容並不重要，重要的是其內查詢語句的結果集空或者非空，空則返回false，非空則返回true。
它的查詢過程類似於以下過程：

• List resultSet={};

• Array A=(select * from A);

• for(int i=0;i<A.length;i++)

• { if(exists(A[i].id) { //執行select 1 from B where B.id=A.id是否有記錄返回



• resultSet.add(A[i]);


• }


• }return resultSet;



當B表比A表數據大時適合使用exists()，因為它沒有那麼多遍歷操作，只需要再執行一次查詢就行。

• 例1：A表有10000條記錄，B表有1000000條記錄，那麼exists()會執行10000次去判斷A表中的id是否與B表中的id相等。

• 例2：A表有10000條記錄，B表有100000000條記錄，那麼exists()還是執行10000次，因為它只執行A.length次，可見B表數據越多，越適合exists()發揮效果。

• 例3：A表有10000條記錄，B表有100條記錄，那麼exists()還是執行10000次，還不如使用in()遍歷10000*100次，因為in()是在內存里遍歷比較，而exists()需要查詢資料庫，我們都知道查詢資料庫所消耗的性能更高，而內存比較很快。

結論：EXISTS()適合B表比A表數據大的情況

3、使用情況分析

當A表數據與B表數據一樣大時，in與exists效率差不多，可任選一個使用。

在插入記錄前，需要檢查這條記錄是否已經存在，只有當記錄不存在時才執行插入操作，可以通過使用 EXISTS 條件句防止插入重復記錄。

• insert into A (name,age) select name,age from B

• where not exists (select 1 from A where A.id=B.id);




EXISTS與IN的使用效率的問題，通常情況下採用exists要比in效率高，因為IN不走索引。但要看實際情況具體使用：

• IN適合於外表大而內表小的情況；

• EXISTS適合於外表小而內表大的情況。

4、關於EXISTS：

EXISTS用於檢查子查詢是否至少會返回一行數據，該子查詢實際上並不返回任何數據，而是返回值True或False。

EXISTS 指定一個子查詢，檢測行的存在。

語法： EXISTS subquery

參數： subquery 是一個受限的 SELECT 語句 (不允許有 COMPUTE 子句和 INTO 關鍵字)。

結果類型： Boolean 如果子查詢包含行，則返回 TRUE ，否則返回 FLASE 。

結論：

• select * from A where exists (select 1 from B where A.id=B.id)



EXISTS(包括 NOT EXISTS )子句的返回值是一個boolean值。 EXISTS內部有一個子查詢語句(SELECT ... FROM...),我將其稱為EXIST的內查詢語句。其內查詢語句返回一個結果集, EXISTS子句根據其內查詢語句的結果集空或者非空，返回一個布爾值。

一種通俗的可以理解為：將外查詢表的每一行，代入內查詢作為檢驗，如果內查詢返回的結果取非空值，則EXISTS子句返回TRUE，這一行行可作為外查詢的結果行，否則不能作為結果。

分析器會先看語句的第一個詞，當它發現第一個詞是SELECT關鍵字的時候，它會跳到FROM關鍵字，然後通過FROM關鍵字找到表名並把表裝入內存。接著是找WHERE關鍵字，如果找不到則返回到SELECT找欄位解析，如果找到WHERE，則分析其中的條件，完成後再回到SELECT分析欄位。最後形成一張我們要的虛表。

WHERE關鍵字後面的是條件表達式。條件表達式計算完成後，會有一個返回值，即非0或0，非0即為真(true)，0即為假(false)。同理WHERE後面的條件也有一個返回值，真或假，來確定接下來執不執行SELECT。

分析器先找到關鍵字SELECT，然後跳到FROM關鍵字將STUDENT表導入內存，並通過指針找到第一條記錄，接著找到WHERE關鍵字計算它的條件表達式，如果為真那麼把這條記錄裝到一個虛表當中，指針再指向下一條記錄。如果為假那麼指針直接指向下一條記錄，而不進行其它操作。一直檢索完整個表，並把檢索

java">

出來的虛擬表返回給用戶。EXISTS是條件表達式的一部分，它也有一個返回值(true或false)。




作者：IronM
鏈接：https://www.jianshu.com/p/f212527d76ff
來源：簡書
著作權歸作者所有。商業轉載請聯系作者獲得授權，非商業轉載請註明出處。

⑶ SQL 中的 in 與 not in、exists 與 not exists 的區別以及性能分析

SQL中，in和exists，以及not in和not exists在使用上有顯著的區別，並且性能受到表大小和查詢條件的影響。

1. 對於in和exists：

in通常通過hash連接操作，效率取決於兩個表的大小，當表相當時，兩者性能相近。如果子查詢表大，用exists；反之，子查詢表小，用in。如表A（小表）與表B（大表）的查詢。

2. not in與not exists：

not in與邏輯上的not exists不完全等同，誤用可能導致錯誤。not in通常全表掃描，效率較低，除非子查詢欄位有非空限制。not exists則利用關聯子查詢，可以利用索引，無論表大小都更優。

3. in與=的區別：

兩者在某些情況下結果相同，但in更強調集合匹配，而=則更為精確。

4. EXISTS的性能分析：

當子查詢結果集小，主表大且有索引時，in效率更高。反之，主表小、子表大且有索引時，exists更優。

5. NOT IN與NOT EXISTS：

兩者在某些需求下不能互換，NOT EXISTS通常更推薦，尤其是在基於成本的優化中。具體使用取決於主查詢和子查詢的大小及欄位約束。

總結來說，in和exists的選擇基於內外表的大小和索引，而not in和not exists則要考慮欄位約束和性能優化。優化SQL時，需根據實際場景靈活運用，沒有絕對的效率高低，應根據具體情況進行調整。

⑷ 在SQL語句中，in和exist的區別是什麼

in是把外表和內表作hash連接，而exists是對外表作loop循環。

⑸ SQL技巧：IN&EXISTS

在構建SQL查詢語句時，IN & NOT IN和EXISTS & NOT EXISTS是兩種常見且重要的條件語句。本文將詳細介紹Oracle資料庫中這兩種操作的基本原理及其潛在的性能影響。

IN操作符允許我們在指定的一系列值中匹配數據。例如，查詢ID為10, 20或30的員工記錄，或表1的員工記錄和表2的ID與score相同的記錄。IN操作符檢查目標列值是否匹配給定列表中的任意值。

EXISTS操作符用於對子查詢進行布爾測試。如果子查詢返回至少一個記錄，則EXISTS返回TRUE，否則返回FALSE。適用於查找存在匹配項的記錄，例如表1的員工記錄和表2的ID與score相同的記錄。

NOT IN和NOT EXISTS與IN和EXISTS操作相對應，分別表示否定的IN和EXISTS條件。

除了IN、EXISTS、NOT IN和NOT EXISTS，INNER JOIN也是一種實現相同功能的方法，但具體效果取決於查詢需求、數據集大小、索引設置和資料庫版本。

在性能比較方面，IN、EXISTS和INNER JOIN方法的具體效率取決於查詢結果集大小、索引設置和資料庫版本。使用執行計劃分析可以直觀地比較不同方法的性能。

總結，IN & NOT IN 和 EXISTS & NOT EXISTS在Oracle資料庫中是強大的工具。理解它們的運作方式和適用場景有助於進行有效的查詢設計。選擇哪種方法取決於特定需求、數據環境和性能測試結果。熟練掌握和靈活運用這些SQL操作符，能夠提升SQL運用水平。