搜尋此網誌

2013年7月31日 星期三

【SQL】使用JOIN觀念

參考來源:Join (SQL) - Wikipedia, the free encyclopediaA Visual Explanation of SQL Joins
  • 大致分為幾類:
    1. Inner Join
      • Natural Join
    2. Outer Join
      1. Left Outer Join
      2. Right Outer Join
      3. Full Outer Join
    3. Cross Join
    4. Self-Join
建立如下測試資料:
 (designated Department.DepartmentID is the primary key and Employee.DepartmentID is a foreign key),建立方法參考按此

Inner Join-只會回傳join條件中有匹配的資料

inner可省略,通常使用join+表格後面需接on+條件。
範例:
SELECT *
FROM employee INNER JOIN department
  ON employee.DepartmentID = department.DepartmentID;

其結果與下面語法、效能相同,但建議使用上面的方法:

SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;

Natural Join-只回傳條件中匹配的資料,但相同欄位名稱只會出現一次

SELECT *
FROM employee NATURAL JOIN department;

Outer Join-回傳單一邊資料表中的資料列,及另一邊資料表中符合join條件中匹配的資料列。

包含Left Outer Join、Right Outer Join和Full Outer Join三種類型,其中Outer關鍵字可省略

- Left Outer Join
以Employee為主,去Join Department。

SELECT *
FROM employee LEFT OUTER JOIN department
  ON employee.DepartmentID = department.DepartmentID;


- Right Outer Join
Department為主,去Join Employee

SELECT *
FROM employee RIGHT OUTER JOIN department
  ON employee.DepartmentID = department.DepartmentID;

- Full Outer Join-即為Left和Right Join的聯集,會回傳左右資料表中所有記錄,不論是否符合Join條件。

SELECT *
FROM employee FULL OUTER JOIN department
  ON employee.DepartmentID = department.DepartmentID;

Cross Join-會回傳兩資料表間的笛卡兒乘積 (Cartesian product),也就是所有可能的排列組合,小心使用,回傳資料很龐大。注意不能指定條件。

SELECT *
FROM employee CROSS JOIN department;

其結果與下面語法、效能相同,但建議使用上面的方法:

SELECT *
FROM employee, department;

Self-Join - 自己和自己做聯集。
舉例想知道每個僱員的上司名稱,可以把自己表格做自我連接。舉例按此

沒有留言: