Programs Knowledge

DEPARTMENTS

DEPT_ID	DEPT_NAME
D1	R&D
D2	IT
D3	HR
D4	Marketing

EMPLOYEES

EMPL_ID	EMPL_NAME	DEPT_ID
001	BRIAN	D1
002	JEFF	D1
003	MARK	D2
004	TOM	D3
005	AMY	NULL

 

下列 [] 內關鍵字表示 可省略，例如：INNER JOIN 和 JOIN 是相同的，用 [] 將 INNER 包起來，是表示：INNER 可省略

[INNER] JOIN

只顥示符合條件的資料列 (左右互相比對)

 

    empl.empl_name 
FROM departments dept
INNER JOIN employees empl ON dept.dept_id = empl.dept_id
ORDER BY empl.empl_id;

--同上方 SQL (Oracle 寫法)
SELECT dept.dept_id, dept.dept_name, empl.empl_id,
    empl.empl_name 
FROM departments dept, employees empl 
WHERE dept.dept_id = empl.dept_id
ORDER BY empl.empl_id;

Output:

DEPT_ID	DEPT_NAME	EMPL_ID	EMPL_NAME
D1	R&D	001	BRIAN
D1	R&D	002	JEFF
D2	IT	003	MARK
D3	HR	004	TOM

[OUTER] JOIN

[OUTER] JOIN 又分成 LEFT [OUTER] JOIN 與 RIGHT [OUTER] JOIN 兩種 （下列皆省略 OUTER 關鍵字）

LEFT JOIN

顥示符合條件的右資料列及左邊不符合條件的資料列 (此時右邊的資料會以 NULL 顯示)

    empl.empl_name 
FROM departments dept
LEFT JOIN employees empl ON dept.dept_id = empl.dept_id
ORDER BY empl.empl_id;

--同上方 SQL (Oracle 寫法)
SELECT dept.dept_id, dept.dept_name, empl.empl_id,
    empl.empl_name 
FROM departments dept, employees empl 
WHERE dept.dept_id = empl.dept_id(+)
ORDER BY empl.empl_id;

Output:

DEPT_ID	DEPT_NAME	EMPL_ID	EMPL_NAME
D1	R&D	001	BRIAN
D1	R&D	002	JEFF
D2	IT	003	MARK
D3	HR	004	TOM
D4	Marketing

RIGHT JOIN

顥示符合條件的左資料列及左邊不符合條件的資料列 (此時左邊的資料會以 NULL 顯示)

    empl.empl_name 
FROM departments dept
RIGHT JOIN employees empl ON dept.dept_id = empl.dept_id
ORDER BY empl.empl_id;

--同上方 SQL (Oracle 寫法)
SELECT dept.dept_id, dept.dept_name, empl.empl_id,
    empl.empl_name 
FROM departments dept, employees empl 
WHERE dept.dept_id(+) = empl.dept_id
ORDER BY empl.empl_id;

Output:

DEPT_ID	DEPT_NAME	EMPL_ID	EMPL_NAME
D1	R&D	001	BRIAN
D1	R&D	002	JEFF
D2	IT	003	MARK
D3	HR	004	TOM
		005	AMY

FULL JOIN

顥示符合條件的料列及左邊+右邊不符合條件的資料列 (此時缺乏資料的資料列會以 NULL 顯示)

empl.empl_name 
FROM departments dept
FULL JOIN employees empl ON dept.dept_id = empl.dept_id
ORDER BY empl.empl_id;

Output:

DEPT_ID	DEPT_NAME	EMPL_ID	EMPL_NAME
D1	R&D	001	BRIAN
D1	R&D	002	JEFF
D2	IT	003	MARK
D3	HR	004	TOM
		005	AMY
D4	Marketing

CROSS JOIN

直接將一個資料表的每一筆資料列和另一個資料表的每一筆資料列搭配成新的資料列

SELECT *
   FROM departments, employees 

SELECT *
   FROM departments
  CROSS JOIN employees 

CROSS JOIN 所產生的結果相當於 笛卡兒積 (http://zh.wikipedia.org/wiki/%E7%AC%9B%E5%8D%A1%E5%84%BF%E7%A7%AF)

               empl.empl_name 
   FROM departments dept
 CROSS JOIN employees empl
 ORDER BY dept.dept_id;

Output:

DEPT_ID	DEPT_NAME	EMPL_ID	EMPL_NAME
D1	R&D	004	TOM
D1	R&D	003	MARK
D1	R&D	002	JEFF
D1	R&D	001	BRIAN
D1	R&D	005	AMY
D2	IT	004	TOM
D2	IT	003	MARK
D2	IT	002	JEFF
D2	IT	001	BRIAN
D2	IT	005	AMY
D3	HR	001	BRIAN
D3	HR	002	JEFF
D3	HR	003	MARK
D3	HR	004	TOM
D3	HR	005	AMY
D4	Marketing	001	BRIAN
D4	Marketing	002	JEFF
D4	Marketing	003	MARK
D4	Marketing	004	TOM
D4	Marketing	005	AMY

Example : http://www.cnblogs.com/kingjiong/archive/2008/11/18/1336159.html