SQL子查詢

進行多資料表查詢，除了使用join外，也可以使用SQL 子查詢(Subquery)，子查詢就是在一個Select指令內再放入一個Select查詢指令進行查詢，通常是位在Select的Where子句，可以透過子查詢取得查詢條件。

子查詢須知

每一個子查詢都是一個Select指令，必須用小括弧包起來，能夠針對不同資料表進行查詢。
如果SQL查詢指令內有子查詢，首先處理的是子查詢條件，然後再依子查詢取得的條件值來處理主查詢，然後就去得最後的查詢結果。

子查詢基本語法:

SELECT 欄位名稱1,欄位名稱2,...,欄位名稱n 
FROM 資料表名稱1
WHERE 欄位名稱 = 
(SELECT 欄位名稱 FROM 資料表名稱2 WHERE 條件)

1.子查詢要在括號()中。
2.通常子查詢SELECT只會取得單一欄位的值，以便主查詢的欄位進行比較運算。
3.如需排序，子查詢不能使用ORDER BY，只能使用GROUP BY 子句。
4.如果子查詢可以取得多筆資料，在主查詢需使用IN邏輯運算子。

在WHERE使用子查詢主要目的是取得其他資料表紀錄的欄位值來建立WHERE句的條件運算式。

範例練習

範例將以下面四個資料表為例。

Customers 客戶資料表

Products 產品資料表

orders 訂單資料表

Orderdetial 訂單資訊資料表

SQL範例1:

在orders資料表查出CustomerID是apple的訂單資訊有幾筆。

select count( * ) as 訂單數 
from orders
where customerID = (select id from Customers where name = 'apple')

上述使用兩個select，分別查詢orders與Customers資料表，在客戶資料表取得apple的ID後，再從orders資料表計算。結果如下:

SQL範例2:

查出Products資料表，price價格大於平均價格的產品。

select *,(select avg(price) from Products )as 平均值 from Products  where price > (select avg(price) from Products)

子查詢不只是只能放在where後，也可以放在select後面的，當欄位輸出的。
上述SQL的三個select分別查詢相同的資料表，其中一個在select後的子查詢，則為創立一個顯示在資料表不存在的平均值欄位，而在where的子查詢查詢出price平均值後與主查詢where做比較。結果如下:

SQL範例3 EXISTS指令:

EXISTS指令:可以判斷子查詢的結果有沒有回傳資料，若有則會執行查詢中的結果。

從Customers資料表，找出orders和Orderdetail資料表有買ProductID為1的產品的客戶資料。

select * from Customers where exists(select customerID from orders where customerID=Customers.id and (select count(*) from Orderdetial where orderID=orders.id and productID=1 )>0)

上述SQL使用三層巢狀查詢，首先要先將有哪些客戶有訂單編號的資料表找出來(select * from Customers where exists(select customerID from orders where customerID=Customers.id) ，接下來在使用and條件從Orderdetial資料表找出orderID要跟orders的id一樣且 productID要=1的大於0的數量(and (select count(*) from Orderdetial where orderID=orders.id and productID=1 )>0))。
這樣就可以從Customers資料表中查出Orderdetail資料表有買ProductID為1的產品的客戶資料。結果如下:

SQL範例4:

使用Orderdetail資料表查出有購買糖果與牛奶糖的訂單資訊。

select * from Orderdetial where exists(select * from Products where (name='糖果' or name='牛奶糖')and id = Orderdetial.productID )

結果如下:

SQL範例5 IN指令:

IN指令可以檢查是否存在子查詢的資料紀錄中。

從Products與Orderdetail資料表找出訂單編號2沒有購買的products。

select * from Products where id not in (select productID from Orderdetial where orderID=2)

從產品資料表中where條件從id中not in找出不在Orderdetial資料表中orderID=2的productID。結果如下:

SQL範例6 in的三層巢狀查詢:

從Products、Orderdetial、orders找出order id =1有買了哪些產品。

select * from Products where id in (select productID from Orderdetial where orderID in (select id from orders where id ='1'))

顯示產品的資料表紀錄，在第二層子查詢中找出產品編號，在第三層子查詢找出訂單編號=1的資料，訂單編號1所購買的產品為糖果、餅乾、巧克力糖。結果如下: