SQL 基礎之多表查詢（十）

JOINS 類型和它的語法

Natural joins（自然連接）:

– NATURAL JOIN 子句

– USING 子句

– ON 子句

自連接

  非等值連接

Outer joins（外連接）:

– LEFT OUTER JOIN（左外連接）

– RIGHT OUTER JOIN（右外連接）

– FULL OUTER JOIN（全外連接）

笛卡爾積

– Cross join（交叉連接）

語法：

select table1.column, table2.column

from table1

[natural join table2] |

[join table2 using (column_name)] |

[join table2

on (table1.column_name = table2.column_name)]|

[left|right|full outer join table2

on (table1.column_name = table2.column_name)]|

[cross join table2];

限制重復的列名

• 在多表中使用表前綴限制列名

• 使用表前綴可以提高效率

• 使用表別名代替全表名前綴

• 表別名提供一個較短的名稱：

    – SQL代碼量更少，使用較少的內存

• 在不同表中具有相同列名的列可以用別名加以區分

創建自然連接

• NATURAL JOIN子句，會以兩個表中具有相同名字的列為條件 創建等值連接。

• 在表中查詢滿足等值條件的數據。

• 如果只是列名相同而數據類型不同，則會產生錯誤。

• 如果多個列名符合，都會做為條件。

1、查詢department_id 和department_name 在哪些城市

select department_id,department_name, location_id,city from departments natural join locations;

使用 USING 子句創建連接

• 如果多個列具有相同的名稱，但自然連接的數據類型又不匹配，則可以使用using子句來指定，使用一 個等值的列

• 當有多個列匹配時，用using子句匹配唯一的列

• NATURAL JOIN 和 USING 子句互斥

• 不要給選中的列中加上表名前綴或別名

1、查詢employee_id,last_name,location_id 從員工表，并且使用department_id為指定鍵值

select employee_id, last_name,location_id, department_id from employees join departments using (department_id);

ON 子句創建連接

• 自然連接中是以具有相同名字的列為連接條件的    

• 使用ON子句指定要連接任意條件或指定列連接條件

• 這個連接條件是與其它條件分開的

• ON 子句使語句具有更高的易讀性

1、查找employees和 的departments 兩張表員工信息，并按照department_id為條件

select e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id from employees e join departments d on (e.department_id = d.department_id);

使用AND子句或WHERE子句適用附加條件： 查詢manager_id為149的

select e.employee_id, e.last_name, e.department_id,

d.department_id, d.location_id

from employees e join departments d

on (e.department_id = d.department_id)

and e.manager_id = 149;

或者

select e.employee_id, e.last_name, e.department_id,

d.department_id, d.location_id

from employees e join departments d

on (e.department_id = d.department_id)

where e.manager_id = 149 ;

使用 ON 子句自連接

使用條件 WORKER 表 表 MANAGER_ID 等于MANAGER 的EMPLOYEE_ID

select worker.last_name emp, manager.last_name mgr

from employees worker join employees manager

on (worker.manager_id = manager.employee_id);

非等值連接

select e.last_name, e.salary, j.grade_level

from employees e join job_grades j

on e.salary

between j.lowest_sal and j.highest_sal;

使用外連接返回沒有直接匹配的記錄

• 在SQL:1999中，兩個表連接，只返回匹配的行，被稱為內連接。

• 兩個表在連接過程中除了返回滿足連接條件的行以外還返回左（或右）表中不滿足條件的行 ，這種連接稱為左（或右） 外連接。

• 兩個表在連接過程中除了返回滿足連接條件的行以外還返回兩個表中不滿足條件的行 ，這種連接稱為全 外聯接。

左外連接

select e.last_name, e.department_id, d.department_name

from employees e left outer join departments d

on (e.department_id = d.department_id) order by department_id desc;

右外連接

select e.last_name, e.department_id, d.department_name

from employees e right outer join departments d

on (e.department_id = d.department_id) ;

全外連接

select e.last_name, d.department_id, d.department_name

from employees e full outer join departments d

on (e.department_id = d.department_id) ;

笛卡爾積

• 笛卡爾集會在下面條件下產生:

– 連接條件被遺漏

– 連接條件不正確

– 所有表中的所有行互相連接

• 為了避免笛卡爾集，可以在 WHERE 加入有效的連接條件。

創建交叉連接

• 使用 CROSS JOIN子句使連接的表產生叉集。

• 叉集也被稱為在兩個表之間的笛卡爾乘積。

select last_name, department_name from employees cross join departments;

練習題:

1. 為HR部門寫一條查詢語句，要求結果生成所有部分的地址。請使用 LOCATIONS 和 COUNTRIES 表，

要求輸出 location_id，street_address，city，state_province，以及 country。使用自然連接獲得要求的結果

select location_id,street_address,city,state_province,country_id,country_name from locations  natural join countries;

2. HR部門需要一個可以查出所有員工的 last_name，department_id,department_name 的查詢語句

select last_name,department_id,department_name from employees join departments using(department_id);

3.  請查出所有在 Toronto工作的員工的 last_name,job_id,department_id,department_name.

select e.last_name,e.job_id,e.department_id,d.department_name from employees e join departments d

on (e.department_id = d.department_id)

join locations l

on (d.location_id = l.location_id)

where l.city ='Toronto';

如果Toronto 為小寫的toronto可以用下面語句

select e.last_name,e.job_id,e.department_id,d.department_name from employees e join departments d

on (e.department_id = d.department_id)

join locations l

on (d.location_id = l.location_id)

where lower(l.city) ='toronto';

4.  請查詢出每個員工的 last_name,employee_id,經理的 last_name,manager_id。請依次為這

些 列 取 合 適 的 別 名 ”Emeployee”,”EMP#”,”Manager”,”Mgr#”。

select w.last_name "Employee",w.employee_id "EMP#",

m.last_name "Manager",m.employee_id "Mgr#"

from employees w join employees m

on (w.manager_id = m.employee_id);

5、顯示所有員工（包括沒有經理的 King），請根據員工編號進行排序

select w.last_name "employee", w.employee_id "emp#",

m.last_name "manager",m.employee_id "mgr#"

from employees w

left outer join employees m

on (w.manager_id = m.employee_id)

order by 2;

6.  請查詢出每個員工的 department_id，員工的 last_name，以及在同一部門一起工作的同事

select e.department_id department,e.last_name employee,

c.last_name colleague

from employees e join employees c

on (e.department_id = c.department_id)

where e.employee_id <> c.employee_id

order by e.department_id,e.last_name,c.last_name;

7、查 詢 所 有 員 工 的last_name,job_id,department_name,salary,grade_level。

select e.last_name,e.job_id,d.department_name,

e.salary,j.grade_level

from employees e join departments d

on (e.department_id = d.department_id)

join job_grades j

on (e.salary between j.lowest_sal and j.highest_sal);

8、 HR 部門的同事想知道所有在 Davies 之后被雇傭的員工。請幫忙寫出一條 SQL 查出來這些員工的last_name 以及 hire_date

select e.last_name,e.hire_date

from employees e join employees davies

on (davies.last_name='Davies')

where davies.hire_date < e.hire_date;

9.  HR 部門的同事想要找出那些在他們的經理之前被雇傭的員工的 last_name,hire_date，以及他們

經理的 last_name，和 hire_date

select w.last_name,w.hire_date,m.last_name,m.hire_date

from employees w join employees m

on (w.manager_id=m.employee_id)

where w.hire_date < m.hire_date;