SQL-子查詢

SQL-子查詢

下兩次指令

interest

interest_id	interest_name
1	Community Involvement
2	Writing
3	Blogging
4	Learning Languages
5	Photography

student

student_id	student_name	interest_name
1	Adam	Learning Languages
2	Sharon	Writing
3	Rebecca	Photography
4	Nina	Learning Languages
5	Mike	YOGA

想要找出學生的興趣有沒有Photography、Writing

SELECT * FROM student
WHERE interest_name IN(‘Photography’,’Writing’);

想要找出有在興趣列表裡面的學生

SELECT interest_name FROM interest;
搜尋出結果後
再貼到WHERE interest_name IN(‘’’’);
但步驟太多了

結合兩個指令

SELECT * FROM student
WHERE interest_name IN(SELECT interest_name FROM interest);

外層查詢
內層查詢，或稱子查詢

SELECT * FROM student
WHERE interest_name IN( SELECT interest_name FROM interest )

連結也有同樣效果

SELECT * FROM student NATURAL JOIN interest;

子查詢規則

1. 在括號()裡面
2. 沒有自己的分號;
3. 是一個select敘述

作為資料欄的子查詢

student

student_id	student_name	interest_name	major_id
1	Adam	Learning Languages	1
2	Sharon	Writing	1
3	Rebecca	Photography	3
4	Nina	Learning Languages	3
5	Mike	YOGA	2

major

major_id	major_name
1	Computer Engineering
2	Asian History
3	Music Pedagogy

SELECT st.student_name, (SELECT interest_name from interest AS it WHERE st.student_id = it.interest_id )
as new_interest_name FROM student AS st ;

如果是用於作為資料欄的子查詢，一次只能從一個資料欄回傳一個值

非關聯式子查詢

SELECT * FROM student WHERE major
WHERE major = ( SELECT major_name FROM major WHERE major_id = 1 )

當子查詢可以單獨執行，不相依於外層查詢時，它就是非關聯式子查詢

關聯式子查詢

SELECT st.student_name, (SELECT interest_name from interest AS it WHERE st.student_id = = it.interest_id )
as new_interest_name FROM student AS st ;

與非關聯式子查詢相反，子查詢會與外層查詢有相依性
也就是子查詢無法單獨存在，必須與外層查詢共生

關聯式子查詢與 NOT EXISTS搭配

可以在關聯式子查詢的用法中，找出外層查詢不存於子查詢的資料

SELECT * FROM student AS st
WHERE NOT EXISTS
(SELECT * FROM interest AS it
WHERE it.interest_name = st.interest_name)

EXISTS 存在
NOT EXISTS 不存在
這兩種都可以使用