使用 JOIN 查詢

SQL 中的 JOIN 運算，大至包含以下幾種分類

1. INNER JOIN
2. OUTER JOIN
   • LEFT OUTER JOIN
   • RIGHT OUTER JOIN
   • FULL OUTER JOIN
3. CROSS JOIN
4. Self-Joins

INNER 與 OUTER 關鍵字皆可以省略。

INNER JOIN

INNER JOIN 只會回傳 join 條件中有匹配的資料列。

DECLARE @Emp1 table( id int, name nvarchar(10) )
DECLARE @Emp2 table( id int, age int )

insert @Emp1 values (1,'vito'),(2,'shao'),(3,'peter')
insert @Emp2 values (1,20),(3,30),(4,40)

範例：

Select E1.id, E1.name, E2.age
FROM @Emp1 E1
INNER JOIN @Emp2 E2 ON E1.id=E2.id

下面語法意義相同，效能相同，但較建議使用上面寫法

Select E1.id, E1.name, E2.age
FROM @Emp1 E1, @Emp2 E2
WHERE E1.id=E2.id

OUTER JOIN

• OUTER JOIN 回傳的是單一邊資料表中的資料列，以及另一邊資料表中符合 join 條件中匹配的資料列。
• OUTER JOIN 包含：LEFT OUTER JOIN 、 RIGHT OUTER JOIN 、 FULL OUTER JOIN 。
• OUTER 關鍵字可以省略。

LEFT OUTER JOIN

範例：

以下範例以 E1 為主去 JOIN E2。

Select E1.id, E1.name, E2.age
FROM @Emp1 E1
LEFT JOIN @Emp2 E2 ON E1.id=E2.id

RIGHT OUTER JOIN

範例：

以下範例以 E2 為主去 JOIN E1。

Select E2.id, E1.name, E2.age
FROM @Emp1 E1
RIGHT JOIN @Emp2 E2 ON E1.id=E2.id

FULL OUTER JOIN

FULL JOIN 即為 LEFT JOIN 與 RIGHT JOIN 的聯集，它會返回左右資料表中所有的紀錄，不論是否符合 Join 條件。 有時候也可以利用它來找尋不相匹配的資料。

Select E1.id, E1.name, E2.id, E2.age
FROM @Emp1 E1
FULL JOIN @Emp2 E2 ON E1.id=E2.id

PS. 若二個資料表 Join 的欄位有使用 freign key 當做條件約束，那麼 Full Join 結果將等同 Left Join 。

CROSS JOIN

交叉連接為兩個資料表間的笛卡兒乘積 (Cartesian product)，兩個資料表在 Join 時，不能指定任何條件， 它會回傳兩個資料表中所有欄位可能的排列組合出來，資料很可觀，要小心使用。

Select E1.id, E1.name, E2.id, E2.age
FROM @Emp1 E1
CROSS JOIN @Emp2 E2

Self Join

A self-join is when a single table is referenced more than once in the JOIN clause because it uses a different alias each time it refers to the table.

SELECT 
	E.FirstName + ' ' + E.LastName AS 'Employee Name'
	, DS.FirstName + ' ' + DS.LastName AS 'Direct Supervisor'
FROM DimEmployee E 
	INNER JOIN DimEmployee DS ON E.ParentEmployeeKey = DS.EmployeeKey;

• 使用聯結