MySQL里面的子查詢實例
2024-07-24 13:04:34
供稿：網友
一，子選擇基本用法
1，子選擇的定義
子迭擇允許把一個查詢嵌套在另一個查詢當中。比如說：一個考試記分項目把考試事件分為考試（T）和測驗（Q）兩種情形。下面這個查詢就能只找出學生們的考試成績
select * from score where event_id in (select event_id from event where type='T');
2，子選擇的用法（3種）
用子選擇來生成一個參考值
在這種情況下，用內層的查詢語句來檢索出一個數據值，然后把這個數據值用在外層查詢語句的比較操作中。比如說，如果要查詢表中學生們在某一天的測驗成績，就應該使用一個內層查詢先找到這一天的測驗的事件號，然后在外層查詢語句中用這個事件號在成績表里面找到學生們的分數記錄。具體語句為：
select * from score where
id=(select event_id from event where date='2002-03-21' and type='Q');
需要注意的是：在應用這種內層查詢的結果主要是用來進行比較操作的分法時，內層查詢應該只有一個輸出結果才對。看例子，如果想知道哪個美國總統的生日最小，構造下列查詢
select * from president where birth=min(birth)
這個查詢是錯的！因為MySQL不允許在子句里面使用統計函數！min()函數應該有一個確定的參數才能工作!所以我們改用子選擇:
select * from president where birht=(select min(birth) from presidnet);
exists 和 not exists 子選擇
上一種用法是把查間結果由內層傳向外層、本類用法則相反，把外層查詢的結果傳遞給內層。看外部查詢的結果是否滿足內部查間的匹配徑件。這種“由外到內”的子迭擇用法非常適合用來檢索某個數據表在另外一個數據表里面有設有匹配的記錄
數據表t1 數據表t2
I1 C1 I2 C2
1
2
3 A
C 2
3
4 C
A
先找兩個表內都存在的數據
select i1 from t1 where exists(select * from t2 where t1.i1=t2.i2);
再找t1表內存在,t2表內不存在的數據
select i1 form t1 where not exists(select * from t2 where t1.i1=t2.i2);
需要注意：在這兩種形式的子選擇里，內層查詢中的星號代表的是外層查詢的輸出結果。內層查詢沒有必要列出有關數據列的名字，田為內層查詢關心的是外層查詢的結果有多少行。希望大家能夠理解這一點
in 和not in 子選擇
在這種子選擇里面，內層查詢語句應該僅僅返回一個數據列，這個數據列里的值將由外層查詢語句中的比較操作來進行求值。還是以上題為例
先找兩個表內都存在的數據
select i1 from t1 where i1 in (select i2 from t2);
再找t1表內存在,t2表內不存在的數據
select i1 form t1 where i1 not in (select i2 from t2);
好象這種語句更容易讓人理解，再來個例子
比如你想找到所有居住在A和B的學生。
select * from student where state in(‘A','B')
二， 把子選擇查詢改寫為關聯查詢的方法。
1，匹配型子選擇查詢的改寫
下例從score數據表里面把學生們在考試事件（T）中的成績（不包括測驗成績！）查詢出來。
Select * from score where event_id in (select event_id from event where type='T');
可見，內層查詢找出所有的考試事件，外層查詢再利用這些考試事件搞到學生們的成績。
這個子查詢可以被改寫為一個簡單的關聯查詢：
Select score.* from score, event where score.event_id=event.event_id and event.event_id='T';
下例可以用來找出所有女學生的成績。
Select * from score where student_id in (select student_id form student where sex = ‘f');
可以把它轉換成一個如下所示的關聯查詢：
Select * from score
Where student _id =student.student_id and student.sex ='f';
把匹配型子選擇查詢改寫為一個關聯查詢是有規律可循的。下面這種形式的子選擇查詢：
Select * from tablel
Where column1 in (select column2a from table2 where column2b = value);
可以轉換為一個如下所示的關聯查詢：
Select tablel. * from tablel,table2
Where table.column1 = table2.column2a and table2.column2b = value;
(2)非匹配(即缺失)型子選擇查詢的改寫
子選擇查詢的另一種常見用途是查找在某個數據表里有、但在另一個數據表里卻沒有的東西。正如前面看到的那樣，這種“在某個數據表里有、在另一個數據表里沒有”的說法通常都暗示著可以用一個left join 來解決這個問題。請看下面這個子選擇查詢，它可以把沒有出現在absence數據表里的學生(也就是那些從未缺過勤的學生)給查出來：
Select * from student
Where student_id not in (select student_id from absence);
這個子選擇查詢可以改寫如下所示的left join 查詢：
Select student. *
From student left join absence on student.student_id =absence.student_id
Where absence.student_id is null;
把非匹配型子選擇查詢改寫為關聯查詢是有規律可循的。下面這種形式的子選擇查詢：
Select * from tablel
Where column1 not in (select column2 from table2);
可以轉換為一個如下所示的關聯查詢：
Select tablel . *
From tablel left join table2 on tablel.column1=table2.column2
Where table2.column2 is null;
注意：這種改寫要求數據列table2.column2聲明為not null。
