07_多表查询

多表查询目标通过本章学习，您将可以:•使用等值和不等值连接在SELECT语句中查询多个表中的数据。•使用外连接查询不满足连接条件的数据。•使用自连接。从多个表中获取数据EMPLOYEESDEPARTMENTS……笛卡尔集•笛卡尔集会在下面条件下产生:–省略连接条件–连接条件无效–所有表中的所有行互相连接•笛卡尔集的大小是组成它的各个子集的乘积•为了避免笛卡尔集，可以在WHERE加入有效的连接条件。笛卡尔集Cartesianproduct:20x8=160rowsEMPLOYEES(20rows)DEPARTMENTS(8rows)……•Equijoin•Non-equijoin•Outerjoin•Selfjoin连接的类型•Crossjoins•Naturaljoins•Usingclause•Fullortwosidedouterjoins•Arbitraryjoinconditionsforouterjoins适用于SQL:1999的连接:Oracle提供的连接(8i或更早):Oracle连接使用连接在多个表中查询数据。•在WHERE字句中写入连接条件。•在表中有相同列时，在列名之前加上表名前缀。SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column1=table2.column2;等值连接EMPLOYEESDEPARTMENTSForeignkeyPrimarykey……SELECTemployees.employee_id,employees.last_name,employees.department_id,departments.department_id,departments.location_idFROMemployees,departmentsWHEREemployees.department_id=departments.department_id;等值连接…多个连接条件与AND操作符EMPLOYEESDEPARTMENTS……区分重复的列名•使用表名前缀在多个表中区分相同的列。•使用表名可以提高效率。•在不同表中具有相同列名的列可以用别名加以区分。SELECTe.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROMemployeese,departmentsdWHEREe.department_id=d.department_id;表的别名•使用别名可以简化查询。•使用表明前缀可以提高执行效率。连接多个表EMPLOYEESLOCATIONSDEPARTMENTS•连接n个表,至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。…非等值连接EMPLOYEESJOB_GRADESSalaryintheEMPLOYEEStablemustbebetweenlowestsalaryandhighestsalaryintheJOB_GRADEStable.…非等值连接SELECTe.last_name,e.salary,j.grade_levelFROMemployeese,job_gradesjWHEREe.salaryBETWEENj.lowest_salANDj.highest_sal;…外连接EMPLOYEESDEPARTMENTSTherearenoemployeesindepartment190.…外连接语法•使用外连接可以查询不满足连接条件的数据。•外连接的符号是(+)•没有(+)的一侧将显示全部记录,有(+)的一侧显示匹配的记录SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column(+)=table2.column;SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column=table2.column(+);SELECTe.last_name,e.department_id,d.department_nameFROMemployeese,departmentsdWHEREe.department_id(+)=d.department_id;外连接…自连接EMPLOYEES(WORKER)EMPLOYEES(MANAGER)MANAGER_IDintheWORKERtableisequaltoEMPLOYEE_IDintheMANAGERtable.……自连接SELECTworker.last_name||'worksfor'||manager.last_nameFROMemployeesworker,employeesmanagerWHEREworker.manager_id=manager.employee_id;…使用SQL:1999语法连接使用连接从多个表中查询数据：SELECTtable1.column,table2.columnFROMtable1[CROSSJOINtable2]|[NATURALJOINtable2]|[JOINtable2USING(column_name)]|[JOINtable2ON(table1.column_name=table2.column_name)]|[LEFT|RIGHT|FULLOUTERJOINtable2ON(table1.column_name=table2.column_name)];叉集•使用CROSSJOIN子句使连接的表产生叉集。•叉集和笛卡尔集是相同的。SELECT*FROMEMPCROSSJOINDEPT;…自然连接•NATURALJOIN子句，会以两个表中具有相同名字的列为条件创建等值连接。•在表中查询满足等值条件的数据。SELECTdepartment_id,department_name,location_id,cityFROMdepartmentsNATURALJOINlocations;自然连接使用USING子句创建连接•使用USING可以在有多个列满足条件时进行选择。•不要给选中的列中加上表名前缀或别名。SELECTe.employee_id,e.last_name,d.location_idFROMemployeeseJOINdepartmentsdUSING(department_id);USING子句…使用ON子句创建连接•自然连接中是以具有相同名字的列为连接条件的。•可以使用ON子句指定额外的连接条件。•这个连接条件是与其它条件分开的。•ON子句使语句具有更高的易读性。SELECTe.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROMemployeeseJOINdepartmentsdON(e.department_id=d.department_id);ON子句…使用ON子句创建多表连接SELECTemployee_id,city,department_nameFROMemployeeseJOINdepartmentsdONd.department_id=e.department_idJOINlocationslONd.location_id=l.location_id;…内连接•在SQL:1999中，内连接只返回满足连接条件的数据。SELECTe.last_name,e.department_id,d.department_nameFROMemployeeseLEFTOUTERJOINdepartmentsdON(e.department_id=d.department_id);LEFTOUTERJOIN…SELECTe.last_name,e.department_id,d.department_nameFROMemployeeseRIGHTOUTERJOINdepartmentsdON(e.department_id=d.department_id);RIGHTOUTERJOIN…SELECTe.last_name,e.department_id,d.department_nameFROMemployeeseFULLOUTERJOINdepartmentsdON(e.department_id=d.department_id);FULLOUTERJOIN…SELECTe.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROMemployeeseJOINdepartmentsdON(e.department_id=d.department_id)ANDe.manager_id=149;AdditionalConditions