Oracle基本数据查询(二)

--自连接（同一个表进行连接， 必须会用不同的表别名来实现连接）  
SELECT W.FIRST_NAME||' '||W.LAST_NAME||' WORKS FOR '  
||M.FIRST_NAME||' '||M.LAST_NAME  
FROM employees W, employees M  
WHERE W.manager_id = M.employee_id  
ORDER BY W.first_name;  
SELECT W.FIRST_NAME||' '||W.LAST_NAME||' WORKS FOR '  
||M.FIRST_NAME||' '||NVL(M.LAST_NAME, 'shareholders')  
FROM employees W, employees M  
WHERE W.manager_id = M.employee_id (+)  
ORDER BY W.first_name;  
--内连接  
SELECT P.NAME, PT.NAME  
FROM products P INNER JOIN product_types PT  
ON p.product_type_id = pt.product_type_id（+）  
ORDER BY p.name;  
SELECT E.FIRST_NAME, E.LAST_NAME, E.SALARY, SG.SALARY_GRADE_ID  
FROM employees E INNER JOIN salary_grades SG  
ON e.salary BETWEEN sg.low_salary AND sg.high_salary  
ORDER BY sg.salary_grade_id;  
 
--使用USING关键字(使用USING关键字的时候最好不要使用表别名，否则会出错)  
SELECT P.NAME, PT.NAME  
FROM products P INNER JOIN product_types PT  
USING(product_type_id)  
ORDER BY p.name;  
--多表内连接  
SELECT C.LAST_NAME, P.NAME AS PRODUCT, PT.NAME AS TYPE  
FROM customers C INNER JOIN purchases PR  
USING (customer_id)  
INNER JOIN products P  
USING (product_id)  
INNER JOIN product_types PT  
USING (product_type_id)  
ORDER BY p.name;  
--多列内连接  
--SELECT * FROM  
--TABLE1 INNER JOIN TABLE2  
--USING(COLUMN1,COLUMN2,…);  
--使用USING关键字的外连接  
--左外连接  
SELECT P.NAME, PT.NAME   
FROM products P LEFT OUTER JOIN product_types PT  
USING(product_type_id);  
--右外连接  
SELECT P.NAME, PT.NAME   
FROM products P RIGHT OUTER JOIN product_types PT  
USING(product_type_id);  
--全外连接  
SELECT P.NAME, PT.NAME   
FROM products P FULL OUTER JOIN product_types PT  
USING(product_type_id);