這篇文章來講一下數(shù)據(jù)庫中的多表查詢

目錄

1. 一個案例引發(fā)的多表連接

1.1 案例說明

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

1.3 案例分析與問題解決

2. 多表查詢分類講解

2.1 等值連接 vs 非等值連接

2.1.1等值連接

2.1.2 非等值連接

2.2 自連接 vs 非自連接?

2.3 內(nèi)連接 vs 外連接?

3.實現(xiàn)多表查詢

3.1 基本語法

3.2 內(nèi)連接(INNER JOIN)的實現(xiàn)

3.3 外連接(OUTER JOIN)的實現(xiàn)?

3.3.1 左外連接(LEFT OUTER JOIN)

3.3.2 右外連接(RIGHT OUTER JOIN)?

3.3.3 滿外連接(FULL OUTER JOIN)

4. UNION的使用

5. 7種SQL JOINS的實現(xiàn)?

5.1 代碼實現(xiàn)

5.2 語法格式小結(jié)

6. SQL99語法新特性

6.1 自然連接

6.2 USING連接

7. 章節(jié)小結(jié)

多表查詢，也稱為關(guān)聯(lián)查詢，指兩個或更多個表一起完成查詢操作。

前提條件：這些一起查詢的表之間是有關(guān)系的（一對一、一對多），它們之間一定是有關(guān)聯(lián)字段，這個 關(guān)聯(lián)字段可能建立了外鍵，也可能沒有建立外鍵。比如：員工表和部門表，這兩個表依靠“部門編號”進行關(guān)聯(lián)。

1. 一個案例引發(fā)的多表連接

1.1 案例說明

從多個表中獲取數(shù)據(jù)：

?

#案例：查詢員工的姓名及其部門名稱

SELECT last_name, department_name FROM employees, departments;?

?分析錯誤情況：

?SELECT COUNT(employee_id) FROM employees;

#輸出107行

SELECT COUNT(department_id)FROM departments;

#輸出27行

SELECT 107*27 FROM dual;

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

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

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

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

?#查詢員工姓名和所在部門名稱

SELECT last_name,department_name FROM employees,departments;

SELECT last_name,department_name FROM employees CROSS JOIN departments; SELECT last_name,department_name FROM employees INNER JOIN departments; SELECT last_name,department_name FROM employees JOIN departments;

為什么會引發(fā)迪卡爾積？因為你沒有加限制條件，那么關(guān)聯(lián)查詢時就是按笛卡爾積的形式來查。如何解決迪卡爾積？加上關(guān)聯(lián)條件即可。?

1.3 案例分析與問題解決

笛卡爾積的錯誤會在下面條件下產(chǎn)生：

• 省略多個表的連接條件（或關(guān)聯(lián)條件）
• 連接條件（或關(guān)聯(lián)條件）無效
• 所有表中的所有行互相連接

為了避免笛卡爾積， 可以在 WHERE 加入有效的連接條件。

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

SELECT table1.column, table2.column

FROM table1, table2

WHERE table1.column1 = table2.column2;? ? #連接條件

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

上述問題的正確寫法：?

#案例：查詢員工的姓名及其部門名稱

SELECT last_name, department_name

FROM employees, departments

WHERE employees.department_id = departments.department_id

2. 多表查詢分類講解

2.1 等值連接 vs 非等值連接

2.1.1等值連接

解釋：就是兩張表中有相同的屬性，然后如果這兩個屬性中的值也相同，那么就根據(jù)這個屬性把這兩張表給連接起來

例：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：區(qū)分重復(fù)的列名

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

在不同表中具有相同列名的列可以用 表名 加以區(qū)分。

例：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;

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

拓展4：連接多個表

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

2.1.2 非等值連接

例：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.2 自連接 vs 非自連接?

說明：當(dāng)table1和table2本質(zhì)上是同一張表，只是用取別名的方式虛擬成兩張表以代表不同的意義。然后兩個表再進行內(nèi)連接，外連接等查詢。?

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

2.3 內(nèi)連接 vs 外連接?

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

內(nèi)連接: 合并具有同一列的兩個以上的表的行, 結(jié)果集中不包含一個表與另一個表不匹配的行?

外連接: 兩個表在連接過程中除了返回滿足連接條件的行以外還返回左（或右）表中不滿足條件的 行 ，這種連接稱為左（或右） 外連接。沒有匹配的行時, 結(jié)果表中相應(yīng)的列為空(NULL)。

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

在 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 中，只有左外連接和右外連接，沒有滿（或全）外連接。

3.實現(xiàn)多表查詢

3.1 基本語法

使用JOIN...ON子句創(chuàng)建連接的語法結(jié)構(gòu)：

SELECT table1.column, table2.column,table3.column

????????FROM table1

????????????????JOIN table2 ON table1 和 table2 的連接條件

????????????????????????JOIN table3 ON table2 和 table3 的連接條件

它的嵌套邏輯類似我們使用的 FOR 循環(huán)：

for t1 in table1:

????????for t2 in table2:

????????????????if condition1:

????????????????????????for t3 in table3:

????????????????????????????????if condition2:

????????????????????????????????????????output t1 + t2 + t3

SQL采用的這種嵌套結(jié)構(gòu)非常清爽、層次性更強、可讀性更強，即使再多的表進行連接也都清晰 可見。

語法說明：

• 可以使用 ON 子句指定額外的連接條件。
• 這個連接條件是與其它條件分開的。
• ON 子句使語句具有更高的易讀性。
• 關(guān)鍵字 JOIN、INNER JOIN、CROSS JOIN 的含義是一樣的，都表示內(nèi)連接

3.2 內(nèi)連接(INNER JOIN)的實現(xiàn)

語法：

SELECT 字段列表

FROM A表

INNER JOIN B表 ON 關(guān)聯(lián)條件

WHERE 等其他子句;

例：SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id);

例：SELECT employee_id, city, department_name FROM employees e JOIN departments d ON d.department_id = e.department_id JOIN locations l ON d.location_id = l.location_id;

3.3 外連接(OUTER JOIN)的實現(xiàn)?

3.3.1 左外連接(LEFT OUTER JOIN)

語法：

#實現(xiàn)查詢結(jié)果是A

SELECT 字段列表

FROM A表

LEFT JOIN B表

ON 關(guān)聯(lián)條件

WHERE 等其他子句;

例：SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id) ;

3.3.2 右外連接(RIGHT OUTER JOIN)?

語法：

#實現(xiàn)查詢結(jié)果是B

SELECT 字段列表

FROM A表

RIGHT JOIN B表

ON 關(guān)聯(lián)條件

WHERE 等其他子句;

例：SELECT e.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id) ;

注意：LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的標(biāo)準(zhǔn)中，在 SQL92 中不存在， 只能用 (+) 表示。?

3.3.3 滿外連接(FULL OUTER JOIN)

• 滿外連接的結(jié)果 = 左右表匹配的數(shù)據(jù) + 左表沒有匹配到的數(shù)據(jù) + 右表沒有匹配到的數(shù)據(jù)。
• SQL99是支持滿外連接的。使用FULL JOIN 或 FULL OUTER JOIN來實現(xiàn)。
• 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

4. UNION的使用

合并查詢結(jié)果 利用UNION關(guān)鍵字，可以給出多條SELECT語句，并將它們的結(jié)果組合成單個結(jié)果集。合并時，兩個表對應(yīng)的列數(shù)和數(shù)據(jù)類型必須相同，并且相互對應(yīng)。各個SELECT語句之間使用UNION或UNION ALL關(guān)鍵字分隔。

語法：

SELECT column,... FROM table1

UNION [ALL]

SELECT column,... FROM table2

?UNION操作符

UNION 操作符返回兩個查詢的結(jié)果集的并集，去除重復(fù)記錄。

UNION ALL操作符?

UNION ALL操作符返回兩個查詢的結(jié)果集的并集。對于兩個結(jié)果集的重復(fù)部分，不去重。

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

例：SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

方式2：SELECT * FROM employees WHERE email LIKE '%a%' UNION SELECT * FROM employees WHERE department_id>90;

5. 7種SQL JOINS的實現(xiàn)?

?

5.1 代碼實現(xiàn)

?#中圖：內(nèi)連接 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

#左下圖：滿外連接 # 左中圖 + 右上圖 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`;

#右下圖 #左中圖 + 右中圖 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

5.2 語法格式小結(jié)

#左中圖 實現(xiàn)A - A∩B

select 字段列表

from A表 left join B表

on 關(guān)聯(lián)條件

where 從表關(guān)聯(lián)字段 is null and 等其他子句;

#右中圖? 實現(xiàn)B - A∩B

select 字段列表

from A表 right join B表

on 關(guān)聯(lián)條件

where 從表關(guān)聯(lián)字段 is null and 等其他子句;

#左下圖? 實現(xiàn)查詢結(jié)果是A∪B

#用左外的A，union 右外的B

select 字段列表

from A表 left join B表

on 關(guān)聯(lián)條件

where 等其他子句

union

select 字段列表

from A表 right join B表

on 關(guān)聯(lián)條件

where 等其他子句

#右下圖 實現(xiàn)A∪B - A∩B 或 (A - A∩B) ∪ （B - A∩B）

#使用左外的 (A - A∩B) union 右外的（B - A∩B）

select 字段列表

from A表 left join B表

on 關(guān)聯(lián)條件

where 從表關(guān)聯(lián)字段 is null and 等其他子句

union

select 字段列表

from A表 right join B表

on 關(guān)聯(lián)條件

where 從表關(guān)聯(lián)字段 is null and 等其他子句

6. SQL99語法新特性

6.1 自然連接

SQL99 在 SQL92 的基礎(chǔ)上提供了一些特殊語法，比如 NATURAL JOIN 用來表示自然連接。我們可以把 自然連接理解為 SQL92 中的等值連接。它會幫你自動查詢兩張連接表中所有相同的字段，然后進行等值連接 。

在SQL92標(biāo)準(zhǔn)中：

SELECT employee_id,last_name,department_name

FROM employees e JOIN departments d

ON e.`department_id` = d.`department_id` AND e.`manager_id` = d.`manager_id`;

在 SQL99 中你可以寫成：

SELECT employee_id,last_name,department_name

FROM employees e NATURAL JOIN departments d;

6.2 USING連接

當(dāng)我們進行連接的時候，SQL99還支持使用 USING 指定數(shù)據(jù)表里的 同名字段 進行等值連接。但是只能配合JOIN一起使用。比如：

SELECT employee_id,last_name,department_name

FROM employees e JOIN departments d

USING (department_id);

你能看出與自然連接 NATURAL JOIN 不同的是，USING 指定了具體的相同的字段名稱，你需要在 USING 的括號 () 中填入要指定的同名字段。同時使用 JOIN...USING 可以簡化 JOIN ON 的等值連接。它與下面的 SQL 查詢結(jié)果是相同的：

SELECT employee_id,last_name,department_name

FROM employees e ,departments d

WHERE e.department_id = d.department_id;

7. 章節(jié)小結(jié)

表連接的約束條件可以有三種方式：WHERE, ON, USING

• WHERE：適用于所有關(guān)聯(lián)查詢
• ON ：只能和JOIN一起使用，只能寫關(guān)聯(lián)條件。雖然關(guān)聯(lián)條件可以并到WHERE中和其他條件一起 寫，但分開寫可讀性更好。
• USING：只能和JOIN一起使用，而且要求兩個關(guān)聯(lián)字段在關(guān)聯(lián)表中名稱一致，而且只能表示關(guān)聯(lián)字 段值相等

#關(guān)聯(lián)條件

#把關(guān)聯(lián)條件寫在where后面

SELECT last_name,department_name

FROM employees,departments

WHERE employees.department_id = departments.department_id;

#把關(guān)聯(lián)條件寫在on后面，只能和JOIN一起使用

SELECT last_name,department_name

FROM employees INNER JOIN departments

ON employees.department_id = departments.department_id;

SELECT last_name,department_name

FROM employees CROSS JOIN departments

ON employees.department_id = departments.department_id;

SELECT last_name,department_name

FROM employees JOIN departments

ON employees.department_id = departments.department_id;

#把關(guān)聯(lián)字段寫在using()中，只能和JOIN一起使用

#而且兩個表中的關(guān)聯(lián)字段必須名稱相同，而且只能表示= #查詢員工姓名與基本工資 SELECT last_name,job_title

FROM employees INNER JOIN jobs USING(job_id);

#n張表關(guān)聯(lián)，需要n-1個關(guān)聯(lián)條件

#查詢員工姓名，基本工資，部門名稱

SELECT last_name,job_title,department_name

FROM employees,departments,jobs

WHERE employees.department_id = departments.department_id

AND employees.job_id = jobs.job_id;

SELECT last_name,job_title,department_name

FROM employees INNER JOIN departments INNER JOIN jobs

ON employees.department_id = departments.department_id

AND employees.job_id = jobs.job_id;

注意： 我們要 控制連接表的數(shù)量 。多表連接就相當(dāng)于嵌套 for 循環(huán)一樣，非常消耗資源，會讓 SQL 查詢性能下 降得很嚴重，因此不要連接不必要的表。在許多 DBMS 中，也都會有最大連接表的限制。

?文章來源地址http://www.zghlxwxcb.cn/news/detail-463286.html

到了這里，關(guān)于數(shù)據(jù)庫基礎(chǔ)——7.多表查詢的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！