联接可分为以下几类：
内联接（典型的联接运算，使用像 = 或 <> 之类的比较运算符）。包括相等联接和自然联接。
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students 和 courses 表中学生标识号相同的所有行。
外联接。外联接可以是左向外联接、右向外联接或完整外部联接。
在 FROM 子句中指定外联接时，可以由下列几组关键字中的一组指定：
LEFT JOIN 或 LEFT OUTER JOIN。
左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
RIGHT JOIN 或 RIGHT OUTER JOIN。
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
FULL JOIN 或 FULL OUTER JOIN。
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
交叉联接。
交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。
例如，下面的内联接检索与某个出版商居住在相同州和城市的作者：
USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a INNER JOIN publishers AS p
ON a.city = p.city
AND a.state = p.state
ORDER BY a.au_lname ASC, a.au_fname ASC
FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息，请参见使用外联接。
例子：
a表 id name b表 id job parent_id
1 张3 1 23 1
2 李四 2 34 2
3 王武 3 34 4
a.id同parent_id 存在关系
内连接
select a.*,b.* from a inner join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
左连接
select a.*,b.* from a left join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
3 王武 null
右连接
select a.*,b.* from a right join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
完全连接
select a.*,b.* from a full join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
3 王武 null
前言
在位置上很常聽到人家討論SQL的時候，Join來Join去，害我每次都會回頭(因為聽起來實在很像在叫我的名字)。
小的自認為DB與SQL的功力，一向很薄弱。
而training新人時，第一個要解釋table與table之間關係，通常也是會講到join。
我雖然大概知道join是怎麼一回事，不過有時候解釋起來，看新人一臉疑惑的表情，也是讓我頗受傷的。
所以，上次看了呂老師書上的一張圖，
真的深深覺得一張圖加兩張table的範例資料，勝過千言萬語啊。
What is join?
Join，通常稱做交集，而交集是什麼呢？望文生義，看圖說故事，各位看官就會瞭解了。
這張圖，解釋了兩張表之間的關係。
兩張table，我們分別定義為Left table跟Right table。Left就是被交集的table (要稱做資料集合也可以)，Right就是拿來交集的table。
就像A x B = C，我們稱A為被乘數，B為乘數，一樣的意思。
比較常看到的，Left table，就是Select * from [table1] ，這個from的table。
Right table，則是被join的那一張table，
例如
Select * from [table1]
Left join [table2] on [table1].fk=[table2].pk
在這例子，table2就是圖裡面的Right table。
而兩張table join的條件，也就是他們是靠什麼來做交集的，就是on後面的 [table1].fk=[table2].pk。
交集的條件不一定只有一個，Left table與Right table的交集後的結果，也有可能是空集合，也就是無符合的資料。
好，接著我們來給兩張表一些資料。
Table1:
| PK | FK |
| 1 | a1 |
| 2 | a2 |
| 3 | a3 |
Table2:
| PK | Description |
| a2 | a2很棒 |
| a3 | a3很棒 |
| a4 | a4很棒 |
當我們現在join的條件是Table1.FK與Table2.PK。
- Inner Join，就代表Table1與Table2兩者，完全符合交集條件的資料集合。
也就是上面圖裡面，兩個圈圈共同圈起來的那個部分，
SQL的範例，通常inner join我們會直接寫Table1_PK Table1_FK Table2_Description 2 a2 a2很棒 3 a3 a3很棒 - Select * from table1, table2
Where table1.FK=table2.PK
Inner Join代表的是兩個table共同的部分才要篩選出來，所以誰是Left，誰是Right其實沒有多大分別。
於是，以我們的範例資料來說，這個例子會撈出
- Select * from table1, table2
- 除了Inner以外，當然就是Outer了。
Outer又分兩種，一種是左邊的Outer，一種是右邊的Outer。
什麼叫做左邊的Outer，在我們的圖裡面，就是左邊的圈圈全都要出來，而符合交集條件的右邊圈圈，資料也要帶出來。Table1_PK Table1_FK Table2_Description 1 null null 2 a2 a2很棒 3 a3 a3很棒 Table1_PK Table2_PK Table2_Description 2 a2 a2很棒 3 a3 a3很棒 null a4 a4很棒 - Left join的SQL如下：
Select * from table1
Left outer join table2 on table1.FK = table2.PK
在這例子，代表了table1就是我們的Left table，所以Left outer join後，table1所有資料都應該顯示出來，
而符合table1.FK = table2.PK條件的資料，應該會帶出Table2的相關欄位資料。 - Right join的SQL如下：
Select * from table1
Right outer join table2 on table1.FK = table2.PK
在這例子，table2代表我們的Right table，所以Table2所有資料應該顯示出來，並且符合交集條件的資料，應該有table1的欄位資料。
- Left join的SQL如下：
另外要提醒的，outer是可以省略的。而在Join裡面，Where條件扮演著什麼角色？
其實Where的條件，是用來限制Left table與Right table的Scope的。
所以where與join可以說毫無關係，因為是用來分別定義table的篩選條件，而非交集的條件。
希望不會在有朋友搞不清楚，到底什麼條件要放在join的on裡面，
什麼條件該放在where裡面。
用這張圖去思考一下，會幫您釐清許多的問題。
結論
- 上述的所有table，只是一種資料集合。也就是代表，它可以是另外一個子查詢後的結果。
- Left與Right的定義，則是被乘數與乘數的分別。
- Where只是篩選條件，拿來限制Left table與Right table的Scope。
- Inner Join沒有左右的差異，所以可以直接用兩張表的一個where 條件來表示，當然您要寫成inner join的表示法也會更符合這張圖的意義喔。
- Outer Join的Outer可以省略，但Left或Right則不能省略。
複雜或多層的Join，只是像寫程式剝洋蔥或穿衣服一樣，可以想像一行一行的join下來，每次都只有一個被乘數和乘數在做交集而已。
就像
a1 x a2 x a3 x a4，您會先計算a1 x a2後，再 x a3，之後的結果再 x a4一樣。
補充(2011/07/20)：很讚的說明：http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
A Visual Explanation of SQL Joins
October 11, 2007
I thought Ligaya Turmelle's post on SQL joins was a great primer for novice developers. Since SQL joins appear to be set-based, the use of Venn diagrams to explain them seems, at first blush, to be a natural fit. However, like the commenters to her post, I found that the Venn diagrams didn't quite match the SQL join syntax reality in my testing.
I love the concept, though, so let's see if we can make it work. Assume we have the following two tables. Table A is on the left, and Table B is on the right. We'll populate them with four records each.
id name id name -- ---- -- ---- 1 Pirate 1 Rutabaga 2 Monkey 2 Pirate 3 Ninja 3 Darth Vader 4 Spaghetti 4 Ninja
Let's join these tables by the name field in a few different ways and see if we can get a conceptual match to those nifty Venn diagrams.
SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name id name id name -- ---- -- ---- 1 Pirate 2 Pirate 3 Ninja 4 Ninja Inner join produces only the set of records that match in both Table A and Table B.
| |
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name id name id name -- ---- -- ---- 1 Pirate 2 Pirate 2 Monkey null null 3 Ninja 4 Ninja 4 Spaghetti null null null null 1 Rutabaga null null 3 Darth Vader Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null. | |
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name id name id name -- ---- -- ---- 1 Pirate 2 Pirate 2 Monkey null null 3 Ninja 4 Ninja 4 Spaghetti null null Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.
| |
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableB.id IS null id name id name -- ---- -- ---- 2 Monkey null null 4 Spaghetti null null To produce the set of records only in Table A, but not in Table B, we perform the same left outer join, thenexclude the records we don't want from the right side via a where clause. | |
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id IS null OR TableB.id IS null id name id name -- ---- -- ---- 2 Monkey null null 4 Spaghetti null null null null 1 Rutabaga null null 3 Darth Vader To produce the set of records unique to Table A and Table B, we perform the same full outer join, thenexclude the records we don't want from both sides via a where clause. |
There's also a cartesian product or cross join, which as far as I can tell, can't be expressed as a Venn diagram:
SELECT * FROM TableA CROSS JOIN TableB
This joins "everything to everything", resulting in 4 x 4 = 16 rows, far more than we had in the original sets. If you do the math, you can see why this is a very dangerous join to run against large tables.
