多表查询

等值连接

三个表之间的连接：

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                  

select * from employees where employee_id =(
                         select manager_id from employees 
                         where last_name = 'Chen'

);