Oracle 資料庫/10g 高階 SQL
本文件詳細介紹了在 Oracle 10g DBMS 上使用最新查詢方法。
聯接查詢將來自兩個或多個表、檢視或物化檢視的行組合在一起。如果查詢的 FROM 子句中列出了多個表，則 Oracle 資料庫將執行聯接。來自任何表的列都可以在 select 列表中列出。但是，同時存在於兩個表中的列必須進行限定，以避免歧義。
以下查詢返回 2007 年期間從客戶收到的所有付款的抵押資訊。
SELECT customer.account_no, mortgage.mortgage_id, payment.payment_id, payment.amount
FROM customer
JOIN mortgage ON mortgage.customer_id = customer.customer_id
JOIN payment ON payment.mortgage_id = mortgage.mortgage_id
WHERE payment.year = 2007;
編寫相同查詢的另一種方法是
SELECT customer.account_no, mortgage.mortgage_id, payment.payment_id, payment.amount
FROM customer,
mortgage,
payment
WHERE mortgage.customer_id = customer.customer_id
AND payment.mortgage_id = mortgage.mortgage_id
AND payment.year = 2007;
NATURAL JOIN 將兩個包含名稱和資料型別相同的列或多列的表聯接在一起。
以下查詢將客戶表與發票表進行自然聯接，自然聯接利用了同時存在於客戶表和發票表上的 customer_id。它返回未支付任何款項的發票的客戶和發票資料。
SELECT customer_id, invoice_id, customer.first_name, customer.last_name
FROM CUSTOMER
NATURAL JOIN invoice
WHERE invoice.amount_paid = 0;
大多數常用的聯接實際上都是 INNER JOIN。INNER JOIN 聯接兩個或多個表，僅返回滿足聯接條件的行。以下是一些 INNER JOIN 的示例。
這將客戶表和訂單表聯接在一起，將客戶與其訂單聯絡起來。結果包含客戶及其訂單的組合列表，如果客戶沒有訂單，則它們將從結果中省略。
SELECT customer_id, order_id
FROM customer c
INNER JOIN order o ON c.customer_id = o.customer_id;
編寫查詢的另一種方法是
SELECT c.customer_id, o.order_id
FROM customer c, order o
WHERE c.customer_id = o.customer_id;
OUTER JOIN 聯接兩個或多個表，無論聯接條件是否滿足，都會返回所有值。當一個表中存在一個值而在另一個表中不存在時，將在沒有聯接夥伴的記錄的列所在位置使用空值。
有三種特定型別的外部聯接：FULL OUTER JOIN、LEFT OUTER JOIN 和 RIGHT OUTER JOIN。
使用 FULL OUTER JOIN，查詢將返回聯接的任一表的行，無論聯接的表上是否有任何匹配資料。如果不存在匹配資料，則將在原本應存在資料的位置放置空值。
在以下示例中，表中的資料與透過 SQL Loader 定期匯入資料匯入表中的資料同步。然後使用儲存過程來檢視是否有任何新增、更新或刪除，並相應地合併行。
SELECT p.name, p.status, p.description, p.qty, i.name, i.status, i.description, i.qty
FROM product p FULL OUTER JOIN import_product i
ON p.product_code = i.product_code;
使用 LEFT OUTER JOIN，查詢僅在聯接左側指定的表中存在行時才會返回行。如果在聯接右側的表中找不到匹配資料，則將在原本應存在資料的位置放置空值。
以下示例將返回所有客戶及其關聯的案例（如果有）。如果客戶沒有案例，則它只會返回客戶的資料。
SELECT cust.customer_id, case.case_id, case.description
FROM customer cust LEFT OUTER JOIN casefile case
ON cust.case_id = case.case_id;
使用 RIGHT OUTER JOIN，查詢僅在聯接右側指定的表中存在行時才會返回行。如果在聯接左側的表中找不到匹配資料，則將在原本應存在資料的位置放置空值。
以下示例將返回卡車及其貨物的列表。如果卡車沒有貨物，則將在指定貨物 load_id 的欄位中放置一個空值。
SELECT truck.truck_id, cargo.load_id, cargo.description
FROM cargo RIGHT OUTER JOIN truck
ON truck.load_id = cargo.load_id;
UNION 運算子輸出同時存在於兩個結果集中的項。UNION ALL 運算子輸出兩個集合中的所有項，無論兩個集合是否都包含該項。
以下查詢返回所有來自舊金山的客戶，其餘額為 100000 和 500000。
SELECT customer_id FROM customer WHERE city = 'SAN FRANCISCO'
UNION
SELECT customer_id FROM accounts WHERE balance BETWEEN 100000 AND 500000;
MINUS 運算子後面的查詢將從 MINUS 運算子之前的查詢的結果集中刪除。
在以下示例中，查詢的第一部分獲取所有客戶。在查詢的第二部分，非活躍客戶被刪除。最後，在查詢的第三部分，郵政編碼在 80000 和 90000 之間的客戶從集合中刪除。
SELECT customer_id FROM customer
MINUS
SELECT customer_id FROM customer WHERE status = 'I'
MINUS
SELECT customer_id FROM customer WHERE zip BETWEEN 80000 AND 99000;
INTERSECT 運算子僅返回同時存在於兩個查詢中的結果。
以下示例返回所有在洛杉磯有欠款的客戶。
SELECT customer_id FROM customer WHERE city = 'LOS ANGELES'
INTERSECT
SELECT customer_id FROM orders WHERE balance_due > 0;
以下查詢是等效的，它們返回所有來自瑞士的客戶。CASE 語句將單個字元狀態標誌“A”和“I”分別轉換為“ACTIVE”和“INACTIVE”。如果值為 NULL，則返回字串“NULL”。
CASE 語句最簡單的形式指定變數，然後指定要檢查的可能值。
SELECT customer_id,
CASE status
WHEN 'A' THEN 'ACTIVE'
WHEN 'I' THEN 'INACTIVE'
ELSE 'NULL'
END
FROM customer
WHERE country_name = 'SWITZERLAND';
搜尋型 CASE 表示式是 CASE 的更高階形式。它不像一開始就指定要檢查的值，而是每個 WHEN 語句都有一個要檢查的比較。
SELECT customer_id,
CASE
WHEN status = 'A' THEN 'ACTIVE'
WHEN status = 'I' THEN 'INACTIVE'
ELSE 'NULL'
END
FROM customer
WHERE country_name = 'SWITZERLAND';