SQL多表聯(lián)查的幾種方法示例總結(jié)

更新時(shí)間：2024年09月30日 09:37:19 作者：ZZDICT

本文詳細(xì)介紹了SQL中不同類(lèi)型的連接操作,包括內(nèi)連接、左外連接、右外連接、全外連接、交叉連接、自連接及其排除內(nèi)連接的特殊應(yīng)用,每種連接類(lèi)型都提供了語(yǔ)法說(shuō)明和具體示例,幫助理解如何在實(shí)際中應(yīng)用這些連接來(lái)處理和分析數(shù)據(jù),需要的朋友可以參考下

1. 內(nèi)連接（INNER JOIN）
2. 左外連接（LEFT JOIN）
3. 右外連接（RIGHT JOIN）
4. 全外連接（FULL JOIN）
5. 交叉連接（CROSS JOIN）
6. 自連接（SELF JOIN）
7. 左外連接排除內(nèi)連接
8. 右外連接排除內(nèi)連接
9. 全外連接排除內(nèi)連接
總結(jié)
聯(lián)合查詢(xún)
三表聯(lián)查
四表聯(lián)查
總結(jié)

1. 內(nèi)連接（INNER JOIN）

語(yǔ)法：

SELECT 列名
FROM 表1
INNER JOIN 表2 ON 表1.列名 = 表2.列名;

示例：

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

2. 左外連接（LEFT JOIN）

語(yǔ)法：

SELECT 列名
FROM 表1
LEFT JOIN 表2 ON 表1.列名 = 表2.列名;

示例：

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

3. 右外連接（RIGHT JOIN）

語(yǔ)法：

SELECT 列名
FROM 表1
RIGHT JOIN 表2 ON 表1.列名 = 表2.列名;

示例：

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

4. 全外連接（FULL JOIN）

語(yǔ)法：

SELECT 列名
FROM 表1
LEFT JOIN 表2 ON 表1.列名 = 表2.列名
UNION
SELECT 列名
FROM 表1
RIGHT JOIN 表2 ON 表1.列名 = 表2.列名;

示例：

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id
UNION
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

5. 交叉連接（CROSS JOIN）

語(yǔ)法：

SELECT 列名
FROM 表1
CROSS JOIN 表2;

示例：

SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;

6. 自連接（SELF JOIN）

自聯(lián)接（Self Join）是指一個(gè)表與自身進(jìn)行聯(lián)接。這種操作通常用于表中記錄之間的比較或關(guān)聯(lián)。自聯(lián)接可以幫助解決例如員工與其經(jīng)理的關(guān)系、產(chǎn)品與產(chǎn)品之間的關(guān)系等問(wèn)題。

自連接查詢(xún)，可以是內(nèi)連接查詢(xún)，也可以是外連接查詢(xún)。

語(yǔ)法：

SELECT 列名1, 列名2, ...
FROM 表名 AS 別名1
JOIN 表名 AS 別名2
ON 別名1.列名 = 別名2.列名
WHERE 條件;

示例：?jiǎn)T工與經(jīng)理自聯(lián)接

假設(shè)我們有一個(gè) employees 表，如下：

employees 表結(jié)構(gòu)：

id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Carol	1
4	Dave	2

查詢(xún)員工及其經(jīng)理的姓名：

SELECT employees.name AS employee_name, manager.name AS manager_name
FROM employees
LEFT JOIN employees AS manager
ON employees.manager_id = manager.id;

7. 左外連接排除內(nèi)連接

定義：返回左表中所有記錄和右表中匹配的記錄，但排除那些在右表中也有匹配的記錄。

語(yǔ)法：

SELECT 列名
FROM 表1
LEFT JOIN 表2 ON 表1.列名 = 表2.列名
WHERE 表2.列名 IS NULL;

示例：

SELECT employees.name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id
WHERE departments.id IS NULL;

8. 右外連接排除內(nèi)連接

定義：返回右表中所有記錄和左表中匹配的記錄，但排除那些在左表中也有匹配的記錄。

語(yǔ)法：

SELECT 列名
FROM 表1
RIGHT JOIN 表2 ON 表1.列名 = 表2.列名
WHERE 表1.列名 IS NULL;

示例：

SELECT departments.department_name
FROM departments
RIGHT JOIN employees ON departments.id = employees.department_id
WHERE employees.id IS NULL;

9. 全外連接排除內(nèi)連接

定義：返回兩個(gè)表的所有記錄，但排除那些在兩個(gè)表中都匹配的記錄。

語(yǔ)法：

SELECT 列名
FROM 表1
LEFT JOIN 表2 ON 表1.列名 = 表2.列名
WHERE 表2.列名 IS NULL
UNION
SELECT 列名
FROM 表2
RIGHT JOIN 表1 ON 表2.列名 = 表1.列名
WHERE 表1.列名 IS NULL;

示例：

SELECT employees.name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id
WHERE departments.id IS NULL
UNION
SELECT departments.department_name
FROM departments
RIGHT JOIN employees ON departments.id = employees.department_id
WHERE employees.id IS NULL;

總結(jié)

內(nèi)連接：僅返回兩個(gè)表中匹配的記錄。
左外連接：返回左表的所有記錄和右表中匹配的記錄，右表中沒(méi)有匹配的記錄顯示為 NULL。
右外連接：返回右表的所有記錄和左表中匹配的記錄，左表中沒(méi)有匹配的記錄顯示為 NULL。
全外連接：返回兩個(gè)表的所有記錄，通過(guò) UNION 模擬。
交叉連接：返回兩個(gè)表的笛卡爾積。
自連接：表與自身的聯(lián)接，常用于記錄間的比較。
左外連接排除內(nèi)連接：返回左表中的記錄，這些記錄在右表中沒(méi)有匹配項(xiàng)。
右外連接排除內(nèi)連接：返回右表中的記錄，這些記錄在左表中沒(méi)有匹配項(xiàng)。
全外連接排除內(nèi)連接：返回兩個(gè)表的所有記錄，排除那些在兩個(gè)表中都有匹配的記錄。

聯(lián)合查詢(xún)

聯(lián)合查詢(xún)（也稱(chēng)為集合操作）用于將多個(gè) SELECT 查詢(xún)的結(jié)果集合并在一起。

1. UNION

功能：將兩個(gè)或多個(gè) SELECT 查詢(xún)的結(jié)果集合并為一個(gè)結(jié)果集，并去除重復(fù)行。
語(yǔ)法：
```
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
```
注意：所有 SELECT 查詢(xún)必須具有相同數(shù)量的列，并且對(duì)應(yīng)列的類(lèi)型應(yīng)兼容。

2. UNION ALL

功能：將兩個(gè)或多個(gè) SELECT 查詢(xún)的結(jié)果集合并為一個(gè)結(jié)果集，包括所有重復(fù)行。

語(yǔ)法：

SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

注意：比 UNION 更高效，因?yàn)樗蝗ブ亍?/p>

3. INTERSECT

功能：返回兩個(gè) SELECT 查詢(xún)結(jié)果中的交集，即兩個(gè)查詢(xún)中都存在的行。

語(yǔ)法：

SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2;

注意：MySQL 8.0 及之前的版本不直接支持 INTERSECT，可以使用 INNER JOIN 來(lái)實(shí)現(xiàn)類(lèi)似功能。

4. EXCEPT (或 MINUS)

功能：返回在第一個(gè) SELECT 查詢(xún)中存在但在第二個(gè) SELECT 查詢(xún)中不存在的行。
語(yǔ)法：
```
SELECT column1, column2 FROM table1
EXCEPT
SELECT column1, column2 FROM table2;
```
注意：MySQL 8.0 及之前的版本不直接支持 EXCEPT?？梢允褂?nbsp;LEFT JOIN 和 IS NULL 實(shí)現(xiàn)類(lèi)似功能。

三表聯(lián)查

定義：將三個(gè)表通過(guò)指定的連接條件聯(lián)接在一起，通常用于從多個(gè)表中獲取相關(guān)數(shù)據(jù)。

語(yǔ)法格式：

SELECT 列名1, 列名2, ...
FROM 表1
JOIN 表2 ON 表1.列名 = 表2.列名
JOIN 表3 ON 表2.列名 = 表3.列名
WHERE 條件
GROUP BY 列名
HAVING 條件
ORDER BY 列名 [ASC|DESC]
LIMIT n OFFSET m;

假設(shè)我們有以下三個(gè)表：

employees（員工表）：包含員工的基本信息。
departments（部門(mén)表）：包含部門(mén)的信息。
salaries（薪資表）：包含員工的薪資信息。

表結(jié)構(gòu)示例：

employees 表：

id	name	department_id
1	Alice	1
2	Bob	2
3	Carol	1

departments 表：

id	department_name
1	HR
2	IT

salaries 表：

employee_id	salary
1	70000
2	80000
3	75000

查詢(xún)語(yǔ)句：

SELECT employees.name, departments.department_name, salaries.salary
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN salaries ON employees.id = salaries.employee_id;

/*
解釋?zhuān)?

INNER JOIN departments ON employees.department_id = departments.id：

將 employees 表與 departments 表通過(guò) department_id 和 id 列進(jìn)行連接，提取部門(mén)名稱(chēng)。
INNER JOIN salaries ON employees.id = salaries.employee_id：

將 employees 表與 salaries 表通過(guò) id 和 employee_id 列進(jìn)行連接，提取薪資信息。
SELECT employees.name, departments.department_name, salaries.salary：

從連接后的結(jié)果中選擇員工姓名、部門(mén)名稱(chēng)和薪資信息。
*/

四表聯(lián)查

定義：將四個(gè)表通過(guò)指定的連接條件聯(lián)接在一起，用于從多個(gè)表中獲取更復(fù)雜的數(shù)據(jù)。

語(yǔ)法格式：

SELECT 列名1, 列名2, ...
FROM 表1
JOIN 表2 ON 表1.列名 = 表2.列名
JOIN 表3 ON 表2.列名 = 表3.列名
JOIN 表4 ON 表3.列名 = 表4.列名
WHERE 條件
GROUP BY 列名
HAVING 條件
ORDER BY 列名 [ASC|DESC]
LIMIT n OFFSET m;

四表聯(lián)查示例

假設(shè)我們有以下四個(gè)表：

employees（員工表）：包含員工的基本信息。
departments（部門(mén)表）：包含部門(mén)的信息。
salaries（薪資表）：包含員工的薪資信息。
projects（項(xiàng)目表）：包含項(xiàng)目的信息。

表結(jié)構(gòu)示例：

employees 表：

id	name	department_id
1	Alice	1
2	Bob	2
3	Carol	1

departments 表：

id	department_name
1	HR
2	IT

salaries 表：

employee_id	salary
1	70000
2	80000
3	75000

projects 表：

project_id	project_name	department_id
1	Project X	1
2	Project Y	2

查詢(xún)語(yǔ)句：

SELECT employees.name, departments.department_name, salaries.salary, projects.project_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN salaries ON employees.id = salaries.employee_id
INNER JOIN projects ON departments.id = projects.department_id;

/*
解釋?zhuān)?

INNER JOIN departments ON employees.department_id = departments.id：

將 employees 表與 departments 表通過(guò) department_id 和 id 列進(jìn)行連接，提取部門(mén)名稱(chēng)。
INNER JOIN salaries ON employees.id = salaries.employee_id：

將 employees 表與 salaries 表通過(guò) id 和 employee_id 列進(jìn)行連接，提取薪資信息。
INNER JOIN projects ON departments.id = projects.department_id：

將 departments 表與 projects 表通過(guò) department_id 和 department_id 列進(jìn)行連接，提取項(xiàng)目名稱(chēng)。
SELECT employees.name, departments.department_name, salaries.salary, projects.project_name：

從連接后的結(jié)果中選擇員工姓名、部門(mén)名稱(chēng)、薪資信息和項(xiàng)目名稱(chēng)。
*/