Mysql exists和in

標籤：mysql   name   nbsp   soft   loop   select   pre   擷取   get   

今天在學習sql語句時，遇到關於exsits的用法，下面是題目：

表架構

Student(S#,Sname,Sage,Ssex) 學生表 
Course(C#,Cname,T#) 課程表 
SC(S#,C#,score) 成績表 
Teacher(T#,Tname) 教師表

問題：

查詢學過“001”並且也學過編號“002”課程的同學的學號、姓名：

　　即執行主句查詢前，先查詢是否子句是否為真，若存在學過002課程的學生，再執行查詢學過001的，

此時查詢出來的結果，就符合既學過001又學過002。

select Student.`S#`,Student.Sname from Student,SC where Student.`S#`=SC.`S#` and SC.`C#`=‘001‘and exists(
 Select * from SC as SC_2 where SC_2.`S#`=SC.`S#` and SC_2.`C#`=‘002‘); 

由此對比in和exsits的用法：exsits:

exsits子句返回的並非查詢結果，而是布爾值，TRUE或者FALSE，exists對外表用loop逐條查詢，

每次查詢都會查看exists的條件陳述式，當 exists裡的條件陳述式，能夠返回記錄行時(無論記錄行是的多少，只要能返回)，條件就為真，

返回當前loop到的這條記錄，反之如果exists裡的條件陳述式不能返回記錄行，則當前loop到的這條記錄被丟棄，exists的條件就像一個bool條件，

當能返回結果集則為true，不能返回結果集則為 false。

In：

 in查詢相當於多個or條件的疊加，這個比較好理解，比如下面的查詢：

select * from user where userId in (1, 2, 3);

等效於

select * from user where userId = 1 or userId = 2 or userId = 3;

not in與in相反，如下

select * from user where userId not in (1, 2, 3);

等效於

select * from user where userId != 1 and userId != 2 and userId != 3;

總的來說，in查詢就是先將子查詢條件的記錄全都查出來，假設結果集為B，共有m條記錄，然後在將子查詢條件的結果集分解成m個，再進行m次查詢

值得一提的是，in查詢的子條件返回結果必須只有一個欄位，例如

select * from user where userId in (select id from B);

而不能是

select * from user where userId in (select id, age from B);

而exists就沒有這個限制

 

效能方面對比：

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

2: select * from A where A.id in (select id from B);

查詢1.可以轉化以下虛擬碼，便於理解

for ($i = 0; $i < count(A); $i++) {

　　$a = get_record(A, $i); #從A表逐條擷取記錄

　　if (B.id = $a[id]) #如果子條件成立

　　　　$result[] = $a;

}

return $result;

大概就是這麼個意思，其實可以看到,查詢1主要是用到了B表的索引，A表如何對查詢的效率影響應該不大

假設B表的所有id為1,2,3,查詢2可以轉換為

select * from A where A.id = 1 or A.id = 2 or A.id = 3;

這個好理解了，這裡主要是用到了A的索引，B表如何對查詢影響不大。

Mysql exists和in