Mysql中的關聯查詢(內串連，外串連，自串連)

標籤：pid   outer   union   外串連   沒有   注意   查詢語句   code   業務需求   

Mysql中的關聯查詢(內串連，外串連，自串連)

在使用資料庫查詢語句時，單表的查詢有時候不能滿足項目的業務需求，在項目開發過程中，有很多需求都是要涉及到多表的串連查詢，總結一下mysql中的多表關聯查詢

一，內串連查詢

是指所有查詢出的結果都是能夠在串連的表中有對應記錄的。

以t_employee(員工表)和t_dept(部門表)為例：

t_employee表中的記錄如下：dept代表該員工所在的部門

t_dept表中記錄如下：

可以發現，其中人力資源部裡沒有員工（這裡只是舉例，可能與實際不符，但主要在於邏輯關係），而趙七沒有對應的部門，現在想要查詢出員工姓名以及其對應的部門名稱：

此時，就要使用內串連查詢，關鍵字（inner join）

在這裡說一下關聯查詢sql編寫的思路，1，先確定所串連的表，2，再確定所要查詢的欄位，3，確定串連條件以及串連方式

 

1.  select
2.  e.empName,d.deptName
3.  from t_employee e
4.  INNER JOIN t_dept d
5.  ON e.dept = d.id;

查詢的結果如下：

 

其中，沒有部門的人員和部門沒有員工的部門都沒有被查詢出來，這就是內串連的特點，只查詢在串連的表中能夠有對應的記錄，其中e.dept = d.id是串連條件

 

 

二，左外串連查詢

是指以左邊的表的資料為基準，去匹配右邊的表的資料，如果匹配到就顯示，匹配不到就顯示為null。例如：

查詢所有員工姓名以及他所在的部門名稱：在內串連中趙七沒有被查出來，因為他沒有對應的部門，現在想要把趙七也查出來，就要使用左外串連：

1.  SELECT e.empName,d.deptName
2.  from t_employee e
3.  LEFT OUTER JOIN t_dept d
4.  on d.id = e.dept;

 

 

在這裡，t_employee就是左表，也就是基準表，用基準表的資料去匹配右表的資料，所以左表的記錄是全部會查詢出來的，如果右表沒有記錄對應的話就顯示null

查詢結果：

關鍵字是left outer join，等效於left join，在關聯查詢中，做外串連查詢就是左串連查詢，兩者是一個概念

三，右外串連是同理的，只是基準表的位置變化了而已

比如：查詢所有的部門和對應的員工：

 

1.  SELECT e.empName,d.deptName
2.  from t_employee e
3.  RIGHT OUTER JOIN t_dept d
4.  on d.id = e.dept;

這裡只是把left修改成了right，但是基準表變化了，是以右表的資料去匹配左表，所以左外串連能做到的查詢，右外串連也能做到
查詢結果：

 

 

四，全外串連

顧名思義，把兩張表的欄位都查出來，沒有對應的值就顯示null，但是注意：mysql是沒有全外串連的(mysql中沒有full outer join關鍵字)，想要達到全外串連的效果，可以使用union關鍵字串連左外串連和右外串連。例如：

 

1.  select e.empName,d.deptName
2.   FROM t_employee e
3.   left JOIN t_dept d
4.   ON e.dept = d.id
5.  UNION
6.  select e.empName,d.deptName
7.   FROM t_employee e
8.   RIGHT JOIN t_dept d
9.   ON e.dept = d.id;

查詢結果：

 

如果在oracle中，直接就使用full outer join關鍵字串連兩表就行了

 

五，自串連查詢

自串連查詢就是當前表與自身的串連查詢，關鍵點在於虛擬化出一張表給一個別名

例如：查詢員工以及他的上司的名稱，由於上司也是員工，所以這裡虛擬化出一張上司表

 

1.  SELECT e.empName,b.empName
2.   from t_employee e
3.   LEFT JOIN t_employee b
4.   ON e.bossId = b.id;

查詢結果：

 

在這裡，b表是虛擬化出的表，我們可以通過查詢瞭解b表的記錄：

 

1.  SELECT e.empName,b.empName,b.*
2.   from t_employee e
3.   LEFT JOIN t_employee b
4.   ON e.bossId = b.id;

查詢結果：

 

後面的四個欄位就是虛擬化出的b表的所有記錄，但看這四個欄位其實就是記錄所有是上司的員工的資訊

所以，自串連查詢一般用作表中的某個欄位的值是引用另一個欄位的值，比如許可權表中，父許可權也屬於許可權。

 

Mysql中的關聯查詢(內串連，外串連，自串連)