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';