oracle多表查询

Click to edit Master title style,First Level,Second Level,Third Level,Fourth Level,Fifth Level,多表查询,目标,通过本章学习，您将可以,:,使用等值和不等值连接在,SELECT,语句中查询多个表中的数据。,使用外连接查询不满足连接条件的数据。,使用自连接。,从多个表中获取数据,EMPLOYEES,DEPARTMENTS,笛卡尔集,笛卡尔集会在下面条件下产生:,省略连接条件,连接条件无效,所有表中的所有行互相连接,为了避免笛卡尔集， 可以在,WHERE,加入有效的连接条件。,笛卡尔集,笛卡尔集,: 20x8=160,行,EMPLOYEES,(20,行,),DEPARTMENTS,(8,行,),Equijoin,Non-equijoin,Outer join,Self join,连接的类型,Cross joins,Natural joins,Using clause,Full or two sided outer joins,Arbitrary join conditions for outer joins,适用于,SQL: 1999,的连接,:,Oracle,提供的连接,(8,i,或更早,):,Oracle,连接,使用连接在多个表中查询数据。,在,WHERE,字句中写入连接条件。,在表中有相同列时，在列名之前加上表名前缀。,SELECT,table1.column, table2.column,FROM,table1, table2,WHERE,table1.column1,=,table2.column2;,等值连接,EMPLOYEES,DEPARTMENTS,外键,主键,SELECT employees.employee_id, employees.last_name,employees.department_id, departments.department_id,departments.location_id,FROM employees, departments,WHERE employees.department_id = departments.department_id;,等值连接,多个连接条件与,AND,操作符,EMPLOYEES,DEPARTMENTS,区分重复的列名,使用表名前缀在多个表中区分相同的列。,使用表名可以提高效率。,在不同表中具有相同列名的列可以用别名加以区分。,SELECT e.employee_id, e.last_name, e.department_id,d.department_id, d.location_id,FROM employees e , departments d,WHERE e.department_id = d.department_id;,表的别名,使用别名可以简化查询。,使用表名前缀可以提高执行效率。,连接多个表,EMPLOYEES,LOCATIONS,DEPARTMENTS,连接,n,个表,至少需要,n-1,个连接条件。 例如：连接三个表，至少需要两个连接条件。,非等值连接,EMPLOYEES,JOB_GRADES,EMPLOYEES,表中的列工资,应在,JOB_GRADES,表中的最高,工资与最低工资之间,非等值连接,SELECT e.last_name, e.salary, j.grade_level,FROM employees e, job_grades j,WHERE e.salary,BETWEEN j.lowest_sal AND j.highest,_sal;,外连接,EMPLOYEES,DEPARTMENTS,190,号部门没有员工,外连接语法,使用外连接可以查询不满足连接条件的数据。,外连接的符号是 (+)。,SELECT,table1.column, table2.column,FROM,table1, table2,WHERE,table1.column,(+),=,table2.column;,SELECT,table1.column, table2.column,FROM,table1, table2,WHERE,table1.column,=,table2.column,(+),;,SELECT e.last_name, e.department_id, d.department_name,FROM employees e, departments d,WHERE e.department_id(+) = d.department_id ;,外连接,自连接,EMPLOYEES (WORKER),EMPLOYEES (MANAGER),WORKER,表中的,MANAGER_ID,和,MANAGER,表中的,MANAGER_ID,相等,自连接,SELECT worker.last_name | works for ,| manager.last_name,FROM employees worker, employees manager,WHERE worker.manager_id = manager.employee_id ;,使用,SQL: 1999,语法连接,使用连接从多个表中查询数据：,SELECT,table1.column, table2.column,FROM,table1,CROSS JOIN,table2, |,NATURAL JOIN,table2, |,JOIN,table2,USING (,column_name,) |,JOIN,table2,ON(,table1.column_name,=,table2.column_name,) |,LEFT|RIGHT|FULL OUTER JOIN,table2,ON (,table1.column_name,=,table2.column_name,);,叉集,使用,CROSS JOIN,子句使连接的表产生叉集。,叉集和笛卡尔集是相同的。,SELECT last_name, department_name,FROM employees,CROSS JOIN departments ;,自然连接,NATURAL JOIN,子句，会以两个表中具有相同名字的列为条件创建等值连接。,在表中查询满足等值条件的数据。,如果只是列名相同而数据类型不同，则会产生错误。,SELECT department_id, department_name,location_id, city,FROM departments,NATURAL JOIN locations ;,自然连接,使用,USING,子句创建连接,在,NATURAL JOIN,子句创建等值连接时，可以使用,USING,子句指定等值连接中需要用到的列。,使用,USING,可以在有多个列满足条件时进行选择。,不要给选中的列中加上表名前缀或别名。,NATURAL JOIN,和,USING,子句经常同时使用。,SELECT e.employee_id, e.last_name, d.location_id,FROM employees e JOIN departments d,USING (department_id) ;,USING,子句,使用,ON,子句创建连接,自然连接中是以具有相同名字的列为连接条件的。,可以使用,ON,子句指定额外的连接条件。,这个连接条件是与其它条件分开的。,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);,ON,子句,使用,ON,子句创建多表连接,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;,内连接,与,外,连接,在,SQL: 1999,中，内连接只返回满足连接条件的数据。,两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行,，这种连接称为左（或右） 外联接。,两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行,，这种连接称为满 外联接。,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,RIGHT OUTER JOIN departments d,ON (e.department_id = d.department_id) ;,右外联接,SELECT e.last_name, e.department_id, d.department_name,FROM employees e,FULL OUTER JOIN departments d,ON (e.department_id = d.department_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 ;,增加连接条件,