等值连接 三个表之间的连接: select e.employee_id,e.department_id,d.department_name,l.city from employees e,departments d ,locations l where e.department_id = d.department_id and l.location_id = d.location_id; EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME CITY ----------- ------------- ------------------------------ ------------------------------ 100 90 Executive Seattle 101 90 Executive Seattle 102 90 Executive Seattle 103 60 IT Southlake 注意:连接n个表,至少需要 n-1 个连接条件,如连接三个表至少需要两个连接条件
非等值连接 select distinct grade_level ,lowest_sal,highest_sal from job_grades; GRADE_LEVEL LOWEST_SAL HIGHEST_SAL ----------- ---------- ----------- E 15000 24999 C 6000 9999 D 10000 14999 F 25000 40000 A 1000 2999 B 3000 5999
select distinct e.employee_id, 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; EMPLOYEE_ID LAST_NAME SALARY GRADE_LEVEL ----------- ------------------------- ---------- ----------- 201 Hartstein 13000 D 205 Higgins 12000 D 170 Fox 9600 C 153 Olsen 8000 C 与等值连接的不同在与过滤条件 没有过滤条件会出现笛卡尔积错误
-- 左外连接(左外联接): 左表中多一个, 需要在右表中加上一个 select e.last_name,e.department_id,d.department_name from employees e,departments d where e.department_id = d.department_id( ); LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- ------------------------------ Wha_len 10 Administration Fay 20 Marketing Gietz 110 Accounting Higgins 110 Accounting Grant 选定了 107 行
右外连接:与左外连接相对应 注意左外连接 和右外连接不能同时存在
-- 两表之间连接和 where 连接条件 效果 相同的 -- join ...on select e.last_name,e.department_id,d.department_name from employees e join departments d on e.department_id = d.department_id; LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- ------------------------------ King 90 Executive Kochhar 90 Executive De Haan 90 Executive Hunold 60 IT
-- 三个表的连接: join...on后面接着join ...on select e.last_name,e.department_id,d.department_name,l.city from employees e join departments d on e.department_id = d.department_id join locations l on d.location_id = l.location_id; LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME CITY ------------------------- ------------- ------------------------------ ------------------------------ King 90 Executive Seattle Kochhar 90 Executive Seattle De Haan 90 Executive Seattle Hunold 60 IT Southlake
--左外连接及右外连接: select e.last_name,e.department_id,d.department_name from employees e left join departments d on e.department_id = d.department_id;
--满外连接 select e.last_name,e.department_id,d.department_name from employees e full join departments d on e.department_id = d.department_id;
-- 自连接 --查询公司中员工 'Chen' 的manger的信息 select emp.last_name,manager.last_name,manager.salary,manager.email from employees emp,employees manager where emp.manager_id = manager.employee_id and lower(emp.last_name) = 'chen'; LAST_NAME LAST_NAME SALARY EMAIL ------------------------- ------------------------- ---------- ------------------------- Chen Greenberg 12000 NGREENBE 来源:https://www./content-4-664801.html |
|