【SQL】使用JOIN觀念

參考來源：Join (SQL) - Wikipedia, the free encyclopedia、A 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 - 自己和自己做聯集。
舉例想知道每個僱員的上司名稱，可以把自己表格做自我連接。舉例按此。

【SQL】使用Create指令

如何建立如右二表？

CREATE TABLE department
(
 DepartmentID INT,
 DepartmentName VARCHAR(20)
);
 
CREATE TABLE employee
(
 LastName VARCHAR(20),
 DepartmentID INT
);
 
INSERT INTO department(DepartmentID, DepartmentName) VALUES(31, 'Sales');
INSERT INTO department(DepartmentID, DepartmentName) VALUES(33, 'Engineering');
INSERT INTO department(DepartmentID, DepartmentName) VALUES(34, 'Clerical');
INSERT INTO department(DepartmentID, DepartmentName) VALUES(35, 'Marketing');
 
INSERT INTO employee(LastName, DepartmentID) VALUES('Rafferty', 31);
INSERT INTO employee(LastName, DepartmentID) VALUES('Jones', 33);
INSERT INTO employee(LastName, DepartmentID) VALUES('Steinberg', 33);
INSERT INTO employee(LastName, DepartmentID) VALUES('Robinson', 34);
INSERT INTO employee(LastName, DepartmentID) VALUES('Smith', 34);
INSERT INTO employee(LastName, DepartmentID) VALUES('John', NULL);