等值连接
三个表之间的连接:
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