SQL知识大全（四):SQL中的数据表关联

# UNION 示例
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers

# UION ALL示例
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM Customers

SELECT c1.cust_id, c1.cust_name, c1.cust_contact 
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name 
AND c2.cust_contact = 'Jim Jones'; 

SELECT C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_price 
FROM Customers AS C, Orders AS O, OrderItems AS OI WHERE C.cust_id = O.cust_id 
AND OI.order_num = O.order_num AND prod_id = 'RGAN01';

SELECT vend_name, prod_name, prod_price 
FROM Vendors INNER JOIN Products 
ON Vendors.vend_id = Products.vend_id;

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products;

SELECT p.name, pt.name
FROM products p, product_types pt
WHERE p.product_type_id = pt.product_type_id(+)
ORDER BY p.name;

SELECT p.name, pt.name
FROM products p FULL JOIN product_types pt ON p.product_type_id = pt.product_type_id
ORDER BY p.name

SELECT p.name, pt.name
FROM products p, product_types pt
WHERE p.product_type_id(+) = pt.product_type_id
ORDER BY p.name;