Subquery

什麼是Subquery

• SELECT中可以加上SELECT子句,內部的select就是子查詢
• 子查詢需要放在()小括弧內
• 提供主要SELECT語句的資料來源或是條件判斷的依據
• 在運算式內或是比較運算符號之後的子查詢,回傳值只能是1個值

SELECT publisherName,(SELECT AVG(price) 
                      FROM book 
                      WHERE book.publisherID=p.publisherID) as Average
FROM publisher p; 

SELECT lastname, firstname
FROM employee
WHERE officeCode IN ( SELECT officeCode 
                      FROM offices 
                      WHERE country='USA' );

SELECT * FROM book
WHERE price <=(SELECT avg(price) from book);

IN

• 單純比較是否與子查詢結果值相同,所以無法使用<>,<,>,<=,>=

SELECT * FROM book
WHERE price IN (select price from book where bookname like '%javas%')       #列出同價位的書

ANY

• 除了值是否相同外,還可以跟子查詢結果的任何一個值比大小,屬於or觀念

SELECT publisherID, publisherName
FROM publisher
WHERE publisherID = ANY(SELECT publisherID FROM book WHERE bookname LIKE '%java%')  

SELECT * FROM book
WHERE price >= ANY(select price from book where bookname like '%javas%')    #列出高於任何一本java的書

ALL

• 跟子查詢結果的每個值比大小,屬於and的觀念

SELECT * FROM book
WHERE price >= ALL(select price from book where bookname like '%javas%')    #列出高於所有java價格的書

EXISTS

• 檢查資料是否存在於子查詢中,如果有回傳true,否則false

SELECT publisherID, publisherName FROM publisher p                           #將所有出版java書籍的出版社列出
WHERE EXISTS (
    SELECT 1 FROM book b
    WHERE p.publisherID = b.publisherID AND bookName like '%java%'
)