数据库表与表之间的连接方式

内连接(INNER JOIN)

内连接返回两个表中符合连接条件的记录。它只返回在两个表中都有匹配记录的行。

 SELECT columns
 FROM table1
 INNER JOIN table2
 ON table1.column = table2.column;

示例:

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

2. 左连接(LEFT JOIN 或 LEFT OUTER JOIN)

左连接返回左表中的所有记录,即使右表中没有匹配的记录。在右表中没有匹配的记录时,结果中对应右表的字段为NULL。

 SELECT columns
 FROM table1
 LEFT JOIN table2
 ON table1.column = table2.column;

示例:

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

3. 右连接(RIGHT JOIN 或 RIGHT OUTER JOIN)

右连接返回右表中的所有记录,即使左表中没有匹配的记录。在左表中没有匹配的记录时,结果中对应左表的字段为NULL。

 SELECT columns
 FROM table1
 RIGHT JOIN table2
 ON table1.column = table2.column;

示例:

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

4. 全连接(FULL JOIN 或 FULL OUTER JOIN)

全连接返回两个表中的所有记录。如果左表或右表中没有匹配的记录,结果中的字段为NULL。MySQL不直接支持FULL JOIN,可以通过UNION实现。

 SELECT columns
 FROM table1
 LEFT JOIN table2
 ON table1.column = table2.column
 UNION
 SELECT columns
 FROM table1
 RIGHT JOIN table2
 ON table1.column = table2.column;

5. 自连接(SELF JOIN)

自连接是表与自身的连接,可以用于比较表中的行。

 SELECT a.columns, b.columns
 FROM table1 a
 INNER JOIN table1 b
 ON a.column = b.column;

示例:

 SELECT e1.name AS employee, e2.name AS manager
 FROM employees e1
 INNER JOIN employees e2
 ON e1.manager_id = e2.id;

6. 交叉连接(CROSS JOIN)

交叉连接返回两个表的笛卡尔积,即没有连接条件,每个左表的记录与右表的每个记录进行组合。

 SELECT columns
 FROM table1
 CROSS JOIN table2;

示例:

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

7. 自然连接(NATURAL JOIN)

自然连接基于具有相同名称和相同数据类型的所有列来进行连接。它隐式地使用这些列来匹配记录。

 SELECT columns
 FROM table1
 NATURAL JOIN table2;

示例:

 SELECT *
 FROM employees
 NATURAL JOIN departments;

假设我们有两个表:employeesdepartments

employees 表:

idnamedepartment_id
1Alice1
2Bob2
3CharlieNULL
4David2
5Eve3

departments 表:

idname
1HR
2Engineering
3Sales
4Marketing

1. 内连接(INNER JOIN)

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

结果:

employee_namedepartment_name
AliceHR
BobEngineering
DavidEngineering
EveSales

2. 左连接(LEFT JOIN)

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

结果:

employee_namedepartment_name
AliceHR
BobEngineering
CharlieNULL
DavidEngineering
EveSales

3. 右连接(RIGHT JOIN)

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

结果:

employee_namedepartment_name
AliceHR
BobEngineering
DavidEngineering
EveSales
NULLMarketing

4. 全连接(FULL JOIN)

MySQL不直接支持FULL JOIN,但可以通过UNION实现:

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

结果:

employee_namedepartment_name
AliceHR
BobEngineering
CharlieNULL
DavidEngineering
EveSales
NULLMarketing

5. 自连接(SELF JOIN)

 SELECT e1.name AS employee, e2.name AS manager
 FROM employees e1
 INNER JOIN employees e2
 ON e1.id = e2.id;

这个例子有些不合适,因为自连接通常用于比较表中的行或查找某些关系(如管理层次结构)。假设有manager_id列:

新的employees 表:

idnamedepartment_idmanager_id
1Alice1NULL
2Bob21
3CharlieNULL1
4David22
5Eve32
 SELECT e1.name AS employee, e2.name AS manager
 FROM employees e1
 INNER JOIN employees e2
 ON e1.manager_id = e2.id;

结果:

employeemanager
BobAlice
CharlieAlice
DavidBob
EveBob

6. 交叉连接(CROSS JOIN)

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

结果:

employee_namedepartment_name
AliceHR
AliceEngineering
AliceSales
AliceMarketing
BobHR
BobEngineering
BobSales
BobMarketing
CharlieHR
CharlieEngineering
CharlieSales
CharlieMarketing
DavidHR
DavidEngineering
DavidSales
DavidMarketing
EveHR
EveEngineering
EveSales
EveMarketing

7. 自然连接(NATURAL JOIN)

假设employeesdepartments表有相同的列名department_id

 SELECT employees.name AS employee_name, departments.name AS department_name
 FROM employees
 NATURAL JOIN departments;

结果:

employee_namedepartment_name
AliceHR
BobEngineering
DavidEngineering
EveSales
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值