等值連接

三個(gè)表之間的連接：

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個(gè)表，至少需要? n-1? 個(gè)連接條件，如連接三個(gè)表至少需要兩個(gè)連接條件

?

非等值連接

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    

與等值連接的不同在與過(guò)濾條件

沒(méi)有過(guò)濾條件會(huì)出現(xiàn)笛卡爾積錯(cuò)誤

?

?

-- 左外連接(左外聯(lián)接):? ?左表中多一個(gè), 需要在右表中加上一個(gè)

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 行 

?

右外連接：與左外連接相對(duì)應(yīng)

注意左外連接 和右外連接不能同時(shí)存在

?

?

-- 兩表之間連接和? 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           

?

-- 三個(gè)表的連接： 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                  

?