Oracle Les06_显示多个表中的数据概要1.pptx

显示多个表中的数据;Objectives;获得多个表中的数据;联结类型;使用Oracle SQL:1999 语法将表联结起来;限定模糊的列名;创建自然联结;SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations ;;SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations ;;SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations WHERE department_id IN （20，50）;;通过USING子句创建联结;用于联结的列名;SELECT employee_id, last_name, location_id, department_id FROM employees JOIN departments USING (department_id) ;;SELECT l.city, d.department_name FROM locations l JOIN departments d USING (location_id) WHERE d.location_id = 1400;;通过ON子句创建联结;SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id);;SELECT employee_id, city, department_name FROM employees e JOIN departments d ON d.department_id = e.department_id JOIN locations l ON d.location_id = l.location_id;;SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id) AND e.manager_id = 149 ;;自然联结总结;自联结;自联结;将表联结到自身;自联结等效传统语法的写法;非等值联结;SELECT a.last_name,a.salary,b.grade_level FROM employees a JOIN job_grades b ON a.salary BETWEEN b.lowest_sal AND highest_sal;SELECT a.last_name,a.salary,b.grade_level FROM employees a,job_grades b WHERE a.salary BETWEEN b.lowest_sal AND highest_sal;;外联结;内联结和外联结;SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id) ;;SELECT e.last_name, e.department_id, d.department_name FROM employees e ,departments d WHERE e.department_id = d.department_id(+);;SELECT e.last_name, d.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id)