SQL中的内連接配接查詢和外連接配接查詢

在項目開發中，單表的查詢肯定無法滿足所有的業務，是以很多時候都是多表的連接配接查詢。接下來總結一下SQL中的多表關聯查詢，以MySQL為例。

前期準備

在資料庫中建立以下幾張表并添加一些資料。

1、部門表(tb_dept)，如下

2、員工表(tb_emp),如下。可以看到後勤中心是沒有員工的，一會看差別。

3、工資表(tb_salary),如下

三張表的關系是，一個部門有多個員工，一個員工有多個工資條。

内連接配接查詢

内連接配接查詢的關鍵字是inner join 或者 join(不建議省略inner)。傳回的是表中存在至少一個比對的資料，那麼不比對的資料肯定就不傳回了，如果都不比對，就是空。

在這裡說一下，寫多表關聯查詢的SQL一般分四步走:1、确定要連接配接的表；2、确定要查詢的字段；3、确定連接配接方式；4、确定關聯條件。

1、查詢所有部門及其下面的員工。(用員工表去連接配接部門表，結果也是一樣的，因為上面說了，内連接配接的方式，傳回的結果一定是比對條件的結果)

SELECT td.dept_id,td.dept_name,te.emp_name 
FROM tb_dept td
INNER JOIN tb_emp te
ON td.dept_id = te.dept_id;
           

可以看到，因為後勤中心下面沒有員工，是以查詢結果中就沒有後勤中心（這裡省略inner也可以，但是不建議這麼做）。

平時，下面這種寫法也是内連接配接，但是可讀性沒有上面的強，是以，忘記這樣的寫法吧。

SELECT td.dept_id,td.dept_name,te.emp_name 
FROM tb_dept td , tb_emp te
WHERE td.dept_id = te.dept_id ;
           

2、查詢所有部門及部門下的員工和員工工資(3張表關聯)

SELECT td.dept_id,td.dept_name,te.emp_name, ts.salary_sum
	FROM tb_dept td
	INNER JOIN tb_emp te
	INNER JOIN tb_salary ts
	ON td.dept_id = te.dept_id AND te.emp_id = ts.emp_id;
           

查詢結果

外連接配接之左外連接配接(也稱左連接配接)

左外連接配接關鍵字是LEFT OUTER JOIN 或LEFT JOIN(還是不建議省略outer，可讀性不強)。左外連接配接查詢是以左邊的表為基準，去比對要連接配接的表，不管是否比對條件都會以基準表的條數傳回結果(這裡明顯不同于内連接配接)，比對到的資料就顯示比對到的資料，沒有比對條件的資料就顯示為null。

1、查詢所有部門及下面的員工

SELECT td.dept_id,td.dept_name,te.emp_name  
FROM tb_dept td
LEFT OUTER JOIN tb_emp te
ON td.dept_id = te.dept_id ;
           

查詢結果

2、查詢所有部門及部門下的員工和員工工資(3張表關聯)

SELECT td.dept_id,td.dept_name,te.emp_name, ts.salary_sum
	FROM tb_dept td
	LEFT OUTER JOIN tb_emp te 
	ON td.dept_id = te.dept_id
	LEFT OUTER JOIN tb_salary ts
	ON  te.emp_id = ts.emp_id;
           

查詢結果

外連接配接之右外連接配接(也稱右連接配接)

右連接配接和左連接配接的理論是一樣的，隻是基準表發生了變化，以右邊被關聯表為基準(将左連接配接的基準表和被關聯表交換位置就等價于右連接配接)。右連接配接的關鍵字是RIGHT OUTER JOIN或RIGHT JOIN(還是不建議省略OUTER )。

1、查詢所有部門及下面的員工

SELECT td.dept_id,td.dept_name,te.emp_name 
FROM tb_dept td
RIGHT OUTER JOIN tb_emp te
ON td.dept_id = te.dept_id; 
           

查詢結果(雖然和上面的内連接配接結果是一樣的，但是要注意差別，兩種方式根本不一樣，假如有一個員工沒有部門) 

2、查詢所有部門及部門下的員工和員工工資(3張表關聯)

SELECT td.dept_id,td.dept_name,te.emp_name ,ts.salary_sum
FROM tb_dept td
RIGHT OUTER JOIN tb_emp te
ON td.dept_id = te.dept_id
RIGHT OUTER JOIN tb_salary ts
ON  te.emp_id = ts.emp_id;
           

外連接配接之全連接配接查詢

全連接配接就是傳回目标表的所有資料，有比對的就顯示，沒有比對的就為null。

mysql沒有全連接配接的關鍵字，oracle有full outer join關鍵字。根據全連接配接的定義，可以使用UNION(去重并集)關鍵字

SELECT td.dept_id,td.dept_name,te.emp_name  
FROM   tb_dept td
LEFT OUTER JOIN tb_emp te
ON td.dept_id = te.dept_id ;
UNION
SELECT td.dept_id,td.dept_name,te.emp_name  
FROM tb_dept td 
RIGHT OUTER JOIN tb_emp te
ON td.dept_id = te.dept_id ;
           

 查詢結果

自連接配接查詢

自連接配接查詢一般用作表中的某個字段的值是引用另一個字段的值。

将tb_emp改造一下，添加一個上司字段，由于上司也是員工，是以直接用emp_id關聯，如下

張三豐有七個弟子，宋遠橋和張翠山分别有個兒子(^_^)

關鍵點在于虛拟出一張上司表，由于上司也是員工，是以屬性都一樣。

1、查詢所有員工及其直屬上司的id和姓名

SELECT te.emp_id ,te.emp_name,tleader.emp_id as leader_id, tleader.emp_name as leader_name 
FROM tb_emp te
LEFT OUTER JOIN tb_emp tleader
ON te.emp_leader = tleader.emp_id;
           

查詢結果

上面的工資表好像設計得不是很好，可以多增加幾條資料，并給一個月份的字段标記差別一下，再做一下上面的示例，這裡偷個懶不改了。

總之，寫多表關聯查詢的SQL的思路就分四步走:1、确定要連接配接的表；2、确定要查詢的字段；3、确定連接配接方式；4、确定關聯條件

此篇完結