多表查詢

1.1 笛卡爾積（或交叉連接）的理解

笛卡爾乘積是一個數(shù)學運算。

假設我有兩個集合 X 和 Y，那么 X 和 Y 的笛卡爾積就是 X 和 Y 的所有可能 組合，也就是第一個對象來自于 X，第二個對象來自于 Y 的所有可能。

組合的個數(shù)即為兩個集合中元素 個數(shù)的乘積數(shù)。

SQL92中，笛卡爾積也稱為 交叉連接，英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交 叉連接。

它的作用就是可以把任意表進行連接，即使這兩張表不相關。

笛卡爾積的錯誤會在下面條件下產生：

• 省略多個表的連接條件（或關聯(lián)條件）
• 連接條件（或關聯(lián)條件）無效
• 所有表中的所有行互相連接 為了避免笛卡爾積，

可以在 WHERE 加入有效的連接條件。

在 WHERE子句中寫入連接條件。

在表中有相同列時，在列名之前加上表名前綴。

1.2 多表查詢分類講解

分類1：等值連接 vs 非等值連接

等值連接

• 拓展1：多個連接條件與 AND 操作符
• 拓展2：區(qū)分重復的列名 多個表中有相同列時，必須在列名之前加上表名前綴。 在不同表中具有相同列名的列可以用 表名加以區(qū)分。
• 拓展3：表的別名 使用別名可以簡化查詢。 列名前使用表名前綴可以提高查詢效率。
• 拓展4：連接多個表，總結：連接 n個表,至少需要n-1個連接條件。比如，連接三個表，至少需要兩個連接條件。

非等值連接

WHERE  e.salary BETWEEN j.lowest_sal AND j.highest_sal;

分類2：自連接 vs 非自連接

自連接

當table1和table2本質上是同一張表，只是用取別名的方式虛擬成兩張表以代表不同的意義。然后兩 個表再進行內連接，外連接等查詢

非自連接

分類3：外連接vs內連接

• 內連接: 合并具有同一列的兩個以上的表的行, 結果集中不包含一個表與另一個表不匹配的行
• 外連接: 兩個表在連接過程中除了返回滿足連接條件的行以外還返回左（或右）表中不滿足條件的 行 ，這種連接稱為左（或右） 外連接。沒有匹配的行時, 結果表中相應的列為空(NULL)。
• 如果是左外連接，則連接條件中左邊的表也稱為 主表，右邊的表稱為 從表。
• 如果是右外連接，則連接條件中右邊的表也稱為 主表，左邊的表稱為 從表。

SQL99語法實現(xiàn)多表查詢使用JOIN…ON子句創(chuàng)建連接的語法結構：

SELECT table1.column, table2.column,table3.column
FROM table1
   JOIN table2 ON table1 和 table2 的連接條件
   JOIN table3 ON table2 和 table3 的連接條件

語法說明：

可以使用 ON 子句指定額外的連接條件。

這個連接條件是與其它條件分開的。

ON 子句使語句具有更高的易讀性。

關鍵字 JOIN、INNER JOIN、CROSS JOIN 的含義是一樣的，都表示內連接

• 內連接(INNER JOIN)的實現(xiàn)

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 關聯(lián)條件
WHERE 等其他子句;

• 外連接(OUTER JOIN)的實現(xiàn)
• 左外連接(LEFT OUTER JOIN)

```mysql
#實現(xiàn)查詢結果是A
 SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 關聯(lián)條件
WHERE 等其他子句;
```

• 右外連接(RIGHT OUTER JOIN)

FROM A表 RIGHT JOIN B表
ON 關聯(lián)條件
WHERE 等其他子句;

• 滿外連接(FULL OUTER JOIN)

滿外連接的結果 = 左右表匹配的數(shù)據(jù) + 左表沒有匹配到的數(shù)據(jù) + 右表沒有匹配到的數(shù)據(jù)。

SQL99是支持滿外連接的。使用FULL JOIN 或 FULL OUTER JOIN來實現(xiàn)。 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

UNION和UNION ALL的使用

• UNION：會執(zhí)行去重操作
• UNION ALL：不會執(zhí)行去重操作
• 注意：執(zhí)行UNION ALL語句時所需要的資源比UNION語句少。如果明確知道合并數(shù)據(jù)后的結果數(shù)據(jù) 不存在重復數(shù)據(jù)，或者不需要去除重復的數(shù)據(jù)，則盡量使用UNION ALL語句，以提高數(shù)據(jù)查詢的效 率

7中JOIN的實現(xiàn)

#中圖：內連接 A∩B
 SELECT employee_id,last_name,department_name
 FROM employees e JOIN departments d
 ON e.`department_id` = d.`department_id`;
 
#左上圖：左外連接
SELECT employee_id,last_name,department_name
 FROM employees e LEFT JOIN departments d
 ON e.`department_id` = d.`department_id`;
 
  #右上圖：右外連接
SELECT employee_id,last_name,department_name
 FROM employees e RIGHT JOIN departments d
 ON e.`department_id` = d.`department_id`;
 
  #左中圖：A - A∩B
 SELECT employee_id,last_name,department_name
 FROM employees e LEFT JOIN departments d
 ON e.`department_id` = d.`department_id`
 WHERE d.`department_id` IS NULL
 
  #右中圖：B-A∩B
 SELECT employee_id,last_name,department_name
 FROM employees e RIGHT JOIN departments d
 ON e.`department_id` = d.`department_id`
 WHERE e.`department_id` IS NULL
 
  SELECT employee_id,last_name,department_name
 FROM employees e LEFT JOIN departments d
 ON e.`department_id` = d.`department_id`
 WHERE d.`department_id` IS NULL
 UNION ALL  #沒有去重操作，效率高
SELECT employee_id,last_name,department_name
 FROM employees e RIGHT JOIN departments d
 ON e.`department_id` = d.`department_id`;
 
  #右下圖
#左中圖 + 右中圖  A ∪B- A∩B 或者 (A -  A∩B) ∪ （B - A∩B）
SELECT employee_id,last_name,department_name
 FROM employees e LEFT JOIN departments d
 ON e.`department_id` = d.`department_id`
 WHERE d.`department_id` IS NULL
 UNION ALL
 SELECT employee_id,last_name,department_name
 FROM employees e RIGHT JOIN departments d
 ON e.`department_id` = d.`department_id`
 WHERE e.`department_id` IS NULL

1.3 自然連接

SQL99 在 SQL92 的基礎上提供了一些特殊語法，比如 NATURAL JOIN 用來表示自然連接。

我們可以把 自然連接理解為 SQL92 中的等值連接。

它會幫你自動查詢兩張連接表中 連接。

1.4 USING連接

tment_id` IS NULL

自然連接

• SQL99 在 SQL92 的基礎上提供了一些特殊語法，比如  NATURAL JOIN 用來表示自然連接。
• 我們可以把 自然連接理解為 SQL92 中的等值連接。它會幫你自動查詢兩張連接表中 連接。

USING連接

• USING 指定了具體的相同的字段名稱，你需要在 USING 的括號 () 中填入要指定的同名字段。
• 同時使用  JOIN...USING 可以簡化 JOIN ON 的等值連接。

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論