嵌套查詢

• • • 三、嵌套查詢
    • • 1. 帶有 in 謂詞的子查詢
      • • 例1: 查詢與 "李四" 在同一個系學習的學生
        • 例2 : 查詢選修了課程名為 "信息系統" 的學生學號和姓名
      • 2. 帶有比較運算符的子查詢
      • 3. 帶有 any (some) 或 all 謂詞的子查詢
      • 4. 帶有 exists 謂詞的子查詢

三、嵌套查詢

在 SQL 語言中, 一個select-from-where 語句稱為一個查詢塊,將一個查詢塊嵌套在另一個和查詢塊的 where 子句或者 having 短語的條件中的查詢稱為 嵌套查詢

例如:

select Sname /*外層查詢或父查詢*/ from Student where Sno in(select Sno /*內層查詢或子查詢*/from SCwhere Cno='2');

本例中, 下層查詢塊 select Sno from SC where Cno=‘2’ 是嵌套在上層查詢塊 select Sname from Student where Sno in 的 where 條件中的
上層的查詢塊稱為外層查詢或父查詢,下層查詢塊稱為內層查詢或子查詢

SQL 語言允許多層嵌套查詢, 即一個子查詢還可以嵌套其他子查詢,需要特別注意的是
子查詢的 select 語句中不能使用 order by 子句, order by子句只能對最終查詢結果排序

嵌套查詢使用戶可以用多個簡單查詢構成復雜的查詢,從而增強 SQL 的查詢能力,以層層嵌套的方式來構造程序正是 SQL 中 “結構化” 的含義所在

1. 帶有 in 謂詞的子查詢

在嵌套查詢中, 子查詢的結果往往是一個集合,所以謂詞 in 是嵌套查詢中最經常使用的謂詞

例1: 查詢與 “李四” 在同一個系學習的學生

先分步來完成此查詢, 然后再構造嵌套查詢

確定 “李四” 所在的系名

select Sdept from Student where Sname='李四'

查找所有再 CS 系學習的學生

select Sno,Sname,Sdept from Student where Sdept='CS'

結果為

Sno Sname Sdept -------------------------------- 201215121 李四 CS 201215122 劉六 CS

將第一步查詢嵌入到第二步查詢的條件中, 構造嵌套查詢如下:

select Sno,Sname,Sdept from Student where Sdept in(select Sdeptfrom Studentwhere Sname='李四');

本例中, 子查詢的查詢條件不依賴于父查詢, 稱為不相關子查詢

一種求解方法是由里向外處理,即先執行子查詢,子查詢的結果用于建立其父查詢的查找條件得到如下的語句:

select Sno,Sname,Sdept from Student where Sdept in('CS')

然后執行該語句

本例中的查詢也可以用自身連接來完成:

select S1.Sno,S1.Sname,S1.Sdept from Student S1, Student S2 where S1.Sdept=S2.Sdept and S2.Sname='李四'

還能用帶 exists 謂詞的子查詢來完成（下方有介紹）

可見,實現一個查詢請求有多種方法,當然不同的方法其執行效率可能會有差別,甚至會差別很大,這就是數據庫編程人員應該掌握的數據庫性能調優技術

例2 : 查詢選修了課程名為 “信息系統” 的學生學號和姓名

本查詢涉及學號,姓名和課程名三個屬性,學號和姓名存放再 Student 表中,課程名存放在 Course 表中,但 Student 與 Course 兩個表之間沒有直接聯系, 必須通過 SC 表建立它們二者直接按的聯系,所以此查詢實際上涉及三個關系

select Sno,Sname // 3. 最后在 Student 關系中取出 Sno 和 Sname from Student where Sno in(select Sno // 2. 然后在 SC 關系中找出選修了3號課程的學生學號from SCwhere Cno in(select Cno // 1. 首先在 Course 關系中找出'信息系統'的課程號,結果為3號from Coursewhere Cname='信息系統'));

本例可以用連接查詢實現:

select Student.Sno,Sname from Student,SC,Course where Student.Sno=SC.Sno andSC.Cno=Course.Cno andCourse.Cname='信息系統';

有些嵌套查詢可以用連接運算替代, 有些是不能替代的
從上面兩個 SQL 語句中可以看到, 查詢涉及多個關系時,用嵌套查詢局部求解層次清楚,易于構造,具有結構化程序設計的優點,
相比于連接運算,目前商用關系數據庫管理系統對嵌套查詢的優化做的還不夠完善,所以在實際應用中,能用用連接運算表達的查詢盡可能采用連接運算

以上兩個 SQL 語句中的子查詢的查詢條件不依賴于父查詢, 這類子查詢稱為不相關子查詢, 不相關子查詢時較簡單的一類子查詢
如果子查詢的查詢條件依賴于父查詢,這類子查詢稱為相關子查詢,整個查詢語句稱為相關嵌套查詢語句

2. 帶有比較運算符的子查詢

帶有比較運算符的子查詢是指父查詢與子查詢之間用比較運算符進行連接,當用戶能確切知道內層查詢返回的是單個值時,可以用 >, < , = , >= , <= , != , 或<> 等比較運算符

• 例1 : 查詢與 '李四 ’ 在同一個系的學生

由于一個學生只能在一個系學習,也就是說內查詢的結果是一個值,因此可以用 = 代替 in

select Sno,Sname,Sdept from Student where Sdept=(select Sdeptfrom Studentwhere Sname='李四');

• 例2 : 找出每個學生超過他自己選修課程平均成績的課程號

select Sno,Cno from SC x where Grade>=(select avg(Grade) //某學生的平均成績from SC ywhere y.Sno=x.Sno);

x 是表 SC 的別名, 又稱為元組變量,可以用來表示 SC 的一個元組,
內層查詢時求一個學生所有選修課程平均成績的, 至于是那個學生的平均成績要看參數 x.Sno 的值, 而該值是和父查詢相關的,因此這類查詢被稱為相關子查詢

這個語句的一種可能的執行過程采用以下三個步驟:

// 1. 從外層查詢中取出 SC 的一個元組 x, 將元組 x 的 Sno 值(201215121) 傳送給內層查詢 select avg(Grade) form SC y where y.Sno='201215121'// 2. 執行內層查詢,得到值 88(平均成績的近似值), 用該值代替內層查詢, 得到外層查詢 select Sno,Cno from SC x where Grade>=88// 3. 執行這個查詢,得到 select Sno,Cno from SC x where Grade>=88;然后從外層查詢取出下一個元組重復上述 1 ~ 3 步驟的處理, 知道外層的 SC 元組全部處理完畢,結果為// 學號 和 課程號 (201215121,1) (201215121,3) (201215122,2)

求解相關子查詢不能像求解不相關子查詢那樣一次將子查詢求解出來,然后求解父查詢, 內層查詢由于與外層查詢有關,因此必須 反復求值

3. 帶有 any (some) 或 all 謂詞的子查詢

子查詢返回單值時可以用比較運算符,但返回多值時要用 any (有的系統用some) 或 all 謂詞修飾符
而使用 any 或 all 謂詞時則必須同時使用比較運算符

表達式含義

>any	大于子查詢結果中的某個值
>all	大于子查詢結果中的所有值
<any	小于子查詢結果中的某個值
<all	小于子查詢結果中的所有值
>=any	大于等于子查詢結果中的某個值
>=all	大于等于子查詢結果中的所有值
<=any	小于等于子查詢結果中的某個值
<=all	小于等于子查詢結果中的所有值
=any	等于子查詢結果中的某個值
=all	等于子查詢結果中的所有值 (通常沒有實際意義)
!= (或<>) any	不等于子查詢結果中的某個值
!= (或<>) all	不等于子查詢結果中的任何一個值

• 例1 : 查詢非計算機系比計算機系任意一個學生年齡小的學生姓名和年齡

select Sname,Sage from Student where Sage<any(select Sagefrom Studentwhere Sdept='CS')and Sdept<>'CS'// Sage<any Sdept<>'CS' 這時父查詢塊的條件-- 結果如下Sname Sage ---------------------------王明 18張力 19

關系型數據庫管理系統執行此查詢時,首先處理子查詢,找出 CS 系中所有學生的年齡,構成以惡搞集合 (20,19)
然后處理父查詢,找所有不是 CS 系且年齡小于 20 或者 19 的學生

本查詢也可以用聚集函數來實現,首先用子查詢找出 CS 系中最大年齡 (20), 然后在父查詢中查所有非 CS 系且年齡小于 20 歲的學生

select Sname,Sage from Student where Sage<(select max(Sage)from Studentwhere Sdept='CS')and Sdept<>'CS';

• 例2 : 查詢非計算機系中比計算機系所有學生年齡都小的學生姓名及年齡

select Sname,Sage from Student where Sage<all(select Sagefrom Studentwhere Sdept='CS')and Sdept<>'CS'-- 查詢結果Sname Sage --------------------王明 18

關系數據庫管理系統執行此查詢時,首先處理子查詢,找出 CS 系中所有學生的年齡,構成一i個集合 (20,19)
然后處理父查詢,找所有不是 CS 系且年齡既小于 20 ,也小于 19 的學生

本查詢同樣也可以用聚集函數實現

select Sname,Sage from Student where Sage<(select min(Sage)from Studentwhere Sdept='CS')and Sdept<>'CS'

事實上,用聚集函數實現子查詢通常比直接用 any 或 all 查詢效率要高

any (或aome), all 謂詞與聚集函數, in 謂詞的等價轉換關系

–<> 或 !=<<=>>=

any	in	–	<max	<=max	>min	>=min
all	–	not in	<min	<=min	>max	>=max

= any 等價于 in 謂詞, <any 等價于 <max ,<>all 等價于 not in 謂詞, <all 等價于 <min

4. 帶有 exists 謂詞的子查詢

帶有 exists 謂詞的子查詢不返回任何數據，只產生邏輯真值 “true” 和邏輯假值 “false”

• 例1 ： 查詢選修了1號課程的學生姓名

本查詢涉及 Student 表和 SC 表，可以在 Student 中依次取每個元組的 Sno 值，用此值去檢查 SC 表，若 SC 中存在這樣的元組，其 Sno 值等于此 Student.Sno 值，并且其 Cno=‘1’，則取此 Student.Sname 送入結果表

select Sname from Student where exists(select *from SCwhere Sno=Student.Sno and Cno='1');

使用存在量詞 exists 后，若內層查詢結果為非空，則外層的 where 子句返回真值，否則返回假值
由 exists 引出的子查詢，其目標列表達式通常都用 * ，因為帶 exists 的子查詢只返回真值或假值，給出列名無實際意義

使用存在量詞 not exists 后，若內層查詢結果為空，則外層的 where 子句返回真值，否則返回假值

• 例2 ： 查詢沒有選修1號課程的學生姓名

select Sname from Student where not exists(select*from SCwhere Sno=Student.Sno and Cno='1');

一些帶 exists 或 not exists 謂詞的子查詢不能被其他形式的子查詢等價替換，但所有帶 in 謂詞，比較運算符，any 和 all 謂詞的子查詢都能用帶 exists 謂詞的子查詢等價替換

// 查詢與 "李四" 在同一個系學習的學生select Sno,Sname,Sdeptfrom Student S1where exists(select*from Student S2where S2.Sdept=S1.Sdept andS2.Sname='李四');

由于帶 exists 量詞的相關子查詢只關心內層查詢是否有返回值，并不需要差具體值，因此其效率并不一定低于不相關子查詢，有時是高效的方法

• 例3：查詢選修了全部課程的學生姓名

sql沒有全稱量詞 （for all），但是可以把帶有全稱量詞的謂詞轉換為等價的帶有存在量詞的謂詞

由于沒有全稱量詞，可將題目的意思轉換成等價的用存在量詞的形式， 查詢這樣的學生：沒有一門課程是他不選修的
從而用 exist / not exist 來實現帶全稱量詞的查詢

select Sname from Student where not exists(select*from Coursewhere not exists(select*from SCwhere Sno=Student.Snoand Cno=Course.Cno));

• 例4：查詢至少選修了學生201215122選修的全部課程的學生號碼

本查詢可以用邏輯蘊涵來表達：查詢學號為x的學生，對所有的課程y，只要201215122學生選修了課程y，則x也選修了y，形式化表示如下：

它的表達的語義為：不存在這樣的課程y，學生201215122選修了y，而學生x沒有選

select distinct Sno from SC SCX where not exists(select*from SC SCYwhere SCY.Sno='201215122' andnot exists(select*from SC SCZwhere SCZ.Sno=SCX.Sno andSCZ.Cno=SCY.Cno));

總結

以上是生活随笔為你收集整理的嵌套查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。