搜索
4-2•••4-3……EMPLOYEESDEPARTMENTS4-4•---------•WHERE4-5SQL:1999•Cross•Natural•Using••Oracle(8i)••••4-6SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column1=table2.column2;SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column1=table2.column2;•WHERE••nn-14-7SELECTemployees.employee_id,employees.last_name,employees.department_id,departments.department_id,departments.location_idFROMemployees,departmentsWHEREemployees.department_id=departments.department_id;SELECTemployees.employee_id,employees.last_name,employees.department_id,departments.department_id,departments.location_idFROMemployees,departmentsWHEREemployees.department_id=departments.department_id;•employeesdepartmentsdepartment_id••=4-8••SELECTe.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROMemployeese,departmentsdWHEREe.department_id=d.department_id;SELECTe.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROMemployeese,departmentsdWHEREe.department_id=d.department_id;4-9SELECTe.last_name,e.salary,j.grade_levelFROMemployeese,job_gradesjWHEREe.salaryBETWEENj.lowest_salANDj.highest_sal;SELECTe.last_name,e.salary,j.grade_levelFROMemployeese,job_gradesjWHEREe.salaryBETWEENj.lowest_salANDj.highest_sal;•employeesjob_gradesemployeesjob_grades•==BETWEEN4-10••+SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column(+)=table2.column;SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column(+)=table2.column;SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column=table2.column(+);SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column=table2.column(+);4-11SELECTe.last_name,e.department_id,d.department_nameFROMemployeese,departmentdWHEREe.department_id(+)=d.department_id;SELECTe.last_name,e.department_id,d.department_nameFROMemployeese,departmentdWHEREe.department_id(+)=d.department_id;4-12EMPLOYEES(WORKER)EMPLOYEES(MANAGER)WORKERMANAGER_IDMANAGEREMPLOYEE_ID……4-13SELECTworker.last_name||’workfor’||manager.last_nameFROMemployeesworker,employeesmanagerWHEREworker.manager_id=manager.employee_id;SELECTworker.last_name||’workfor’||manager.last_nameFROMemployeesworker,employeesmanagerWHEREworker.manager_id=manager.employee_id;4-14SQL:1999SELECTtable1.column,table2.columnFROMtable1[CROSSJOINtable2]|[NATURALJOINtable2]|[JOINtable2USING(column_name)]|[JOINtable2ON(table1.column_name=table2.column_name)]|[LEFT|RIGHT|FULLOUTERJOINtable2ON(table1.column_name=table2.column_name)];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)];4-15CrossCrossSELECTlast_name,department_nameFROMemployeesCROSSJOINdepartments;SELECTlast_name,department_nameFROMemployeesCROSSJOINdepartments;4-16Natural•Natural••4-17NaturalSELECTdepartment_id,department_name,location_id,cityFROMdepartmentsNATURALJOINlocations;SELECTdepartment_id,department_name,location_id,cityFROMdepartmentsNATURALJOINlocations;4-18USING•NATURALUSING•USING••NATURALUSING4-19USINGSELECTe.employee_id,e.last_name,d.location_idFROMemployeeseJOINdepartmentsdUSING(department_id);SELECTe.employee_id,e.last_name,d.location_idFROMemployeeseJOINdepartmentsdUSING(department_id);4-20ON•NATURAL•ON••ON4-21ONSELECTe.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROMemployeeseJOINdepartmentsdON(e.department_id=d.department_id);SELECTe.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROMemployeeseJOINdepartmentsdON(e.department_id=d.department_id);4-22ONSELECTemployee_id,city,department_nameFROMemployeeseJOINdepartmentsdONd.department_id=e.department_idJOINlocationslONd.location_id=l.location_id;SELECTemployee_id,city,department_nameFROMemployeeseJOINdepartmentsdONd.department_id=e.department_idJOINlocationslONd.location_id=l.location_id;4-23INNEROUTER•SQL:1999••4-24SELECTe.last_name,e.department_id,d.department_nameFROMemployeeseLEFTOUTERJOINdepartmentsdON(e.department_id=d.department_id);SELECTe.last_name,e.department_id,d.department_nameFROMemployeeseLEFTOUTERJOINdepartmentsdON(e.department_id=d.department_id);4-25SELECTe.last_name,e.department_id,d.department_nameFROMemployeeseRIGHTOUTERJOINdepartmentsdON(e.department_id=d.department_id);SELECTe.last_name,e.department_id,d.department_nameFROMemployeeseRIGHTOUTERJOINdepartmentsdON(e.department_id=d.department_id);4-26SELECTe.last_name,e.department_id,d.department_nameFROMemployeeseFULLOUTERJOINdepartmentsdON(e.department_id=d.department_id);SELECTe.last_name,e.department_id,d.department_nameFROMemployeeseFULLOUTERJOINdepartmentsdON(e.department_id=d.department_id);4-27SELECTe.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROMemployeeseJOINdepartmentsdON(e.department_id=d.department_id)ANDe.manager_id=149;SELECTe.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROMemployeeseJOINdepartmentsdON(e.depa