mysql多表查詢淺談mysql中等值連接與非等值連接、自連接與非自連接、內連接與外連接問題（一）

06_多表查詢

多表查詢，也稱為關聯查詢，指兩個或更多個表一起完成查詢操作。

前提條件：這些一起查詢的表之間是有關系的（一對一、一對多），它們之間一定是有關聯字段，這個

關聯字段可能建立了外鍵，也可能沒有建立外鍵。比如：員工表和部門表，這兩個表依靠“部門編號”進

行關聯。

1. 一個案例引發的多表連接

1.1案例說明

從多個表中獲取數據：

#案例：查詢員工的姓名及其部門名稱
SELECT last_name, department_name
FROM employees, departments;

查詢結果：

+-----------+----------------------+
| last_name | department_name |
+-----------+----------------------+
| King | Administration |
| King | Marketing |
| King | Purchasing |
| King | Human Resources |
| King | Shipping |
| King | IT |
| King | Public Relations |
| King | Sales |
| King | Executive |
| King | Finance |
| King | Accounting |
| King | Treasury |
...
| Gietz | IT Support |
| Gietz | NOC |
| Gietz | IT Helpdesk |
| Gietz | Government Sales |
| Gietz | Retail Sales |
| Gietz | Recruiting |
| Gietz | Payroll |
+-----------+----------------------+
2889 rows in set (0.01 sec)

分析錯誤情況：

SELECT COUNT(employee_id) FROM employees;
#輸出107行
SELECT COUNT(department_id)FROM departments;
#輸出27行
SELECT 107*27 FROM dual;

我們把上述多表查詢中出現的問題稱為：笛卡爾積的錯誤。

1.2笛卡爾積（或交叉鏈接）的理解

笛卡爾乘積是一個數學運算。假設我有兩個集合 X 和 Y，那么 X 和 Y 的笛卡爾積就是 X 和 Y 的所有可能組合，也就是第一個對象來自于 X，第二個對象來自于 Y 的所有可能。組合的個數即為兩個集合中元素個數的乘積數。

笛卡爾積也稱為交叉連接，英文是 CROSS JOIN。在 SQL99 中也是使用 CROSS JOIN表示交叉連接。它的作用就是可以把任意表進行連接，即使這兩張表不相關。在MySQL中如下情況會出現笛卡爾積：

#出現了笛卡爾積的錯誤
#錯誤的原因：缺少了多表的連接條件

#錯誤的實現方式：每個員工和每個部門都匹配了一遍
SELECT employee_id,department_name
FROM employees,departments;#查詢出來2889條記錄

SELECT employee_id,departmant_name
FROM employees CROSS JOIN departments;#查詢出2899條記錄

SELECT *
FROM employees;  #107條記錄

SELECT 2889 / 107
FROM DUAL;

SELECT *
FROM departments; #27條記錄

1.3案例分析與問題解決

笛卡爾積的錯誤會在下面條件下產生：
- 省略多個表的連接條件（或關聯條件）
- 連接條件（或關聯條件）無效
- 所有表中的所有行互相連接
為了避免笛卡爾積，可以在 WHERE 加入有效的連接條件。

加入連接條件后，查詢語法：

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; #連接條件

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

正確寫法：

#3.多表查詢的正確方式：需要有連接條件
SELECT employee_id,department_name
FROM employees,departments
#兩個表的連接條件
WHERE employees.department_id = departments.department_id;

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

#4.如果查詢語句中出現了多個表中都存在的字段，則不許知名此字段所在的表
SELECT employee_id,department_name,department_id
FROM employees,departments
WHERE employees.department_id = departments.department_id;

[SQL] SELECT employee_id,department_name,department_id
FROM employees,departments
WHERE employees.department_id = departments.department_id;
[Err] 1052 - Column 'department_id' in field list is ambiguous

#如果給表起了別名，一旦在SELECT或WHERE中使用表明的話，則必須使用表的別名，而不能再使用表的原名
#如下的操作是錯誤的
SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.department_id = departments.department_id;

[SQL] SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.department_id = departments.department_id;
[Err] 1054 - Unknown column 'departments.department_id' in 'where clause'

2. 多表查詢分類講解

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

等值連接

SELECT employees.employee_id, employees.last_name,
employees.department_id, departments.department_id,
departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

拓展1：多個連接條件與 AND 操作符

拓展2：區分重復的列名

多個表中有相同列時，必須在列名之前加上表名前綴。
在不同表中具有相同列名的列可以用表名加以區分。

SELECT employees.last_name, departments.department_name,employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

拓展3：表的別名

使用別名可以簡化查詢。
列名前使用表名前綴可以提高查詢效率。

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;

需要注意的是，如果我們使用了表的別名，在查詢字段中、過濾條件中就只能使用別名進行代替，不能使用原有的表名，否則就會報錯。

阿里開發規范：

【強制】對于數據庫中表記錄的查詢和變更，只要涉及多個表，都需要在列名前加表的別名（或表名）進行限定。

說明：對多表進行查詢記錄、更新記錄、刪除記錄時，如果對操作列沒有限定表的別名（或表名），并且操作列在多個表中存在時，就會拋異常。

正例：select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;

反例：在某業務中，由于多表關聯查詢語句沒有加表的別名（或表名）的限制，正常運行兩年后，最近在某個表中增加一個同名字段，在預發布環境做數據庫變更后，線上查詢語句出現出 1052 異常：Column 'name' in field list is ambiguous。

拓展4：連接多個表

總結：連接 n個表,至少需要n-1個連接條件。比如，連接三個表，至少需要兩個連接條件。

練習：查詢出公司員工的 last_name,department_name, city

非等值連接

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

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

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

題目：查詢employees表，返回“Xxx works for Xxx”

SELECT CONCAT(worker.last_name ,' works for '
, manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;

練習：查詢出last_name為 ‘Chen’ 的員工的 manager 的信息。

#7.2  自連接     VS   非自連接
SELECT *
FROM employees;


#練習：查詢員工姓名及其管理者的id和姓名[SQL] SELECT emp.employee_id,last_name,employee_id,last_name
from employees emp,employees mgr
WHERE emp.manager_id = mgr.employee_id;
[Err] 1052 - Column 'last_name' in field list is ambiguous

#正確
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
from employees emp,employees mgr
WHERE emp.manager_id = mgr.employee_id;

#MySQL中


[SQL] SELECT e.last_name,d.department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id(+);
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 3

[SQL] #滿外連接： MySQl不支持FULL OUTER JOIN
SELECT e.last_name,d.department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN departments d
ON e.department_id = d.department_id' at line 3

分類3：內連接 vs 外連接

除了查詢滿足條件的記錄以外，外連接還可以查詢某一方不滿足條件的記錄。

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

如果是右外連接，則連接條件中右邊的表也稱為主表，左邊的表稱為從表。

SQL92：使用(+)創建連接

在 SQL92 中采用（+）代表從表所在的位置。即左或右外連接中，(+) 表示哪個是從表。
Oracle 對 SQL92 支持較好，而 MySQL 則不支持 SQL92 的外連接。

#左外連接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
#右外連接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;

而且在 SQL92 中，只有左外連接和右外連接，沒有滿（或全）外連接。

posted @ 2022-05-11 14:22 輕狂書生han 閱讀(535) 評論(0) 收藏舉報

刷新頁面返回頂部

輕狂書生han

mysql多表查詢 淺談mysql中等值連接與非等值連接、自連接與非自連接、內連接與外連接問題（一）