在SQL中，JOIN语法用于结合来自两个或多个表的数据。不同类型的JOIN会基于不同的条件来合并表中的数据。以下是几种常见的JOIN及其差异：
假设我们有两个表：employees 和 departments。
employees 表:
| employee_id | name | department_id |
|---|---|---|
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Charlie | NULL |
| 4 | David | 10 |
departments 表:
| department_id | department_name |
|---|---|
| 10 | HR |
| 20 | Engineering |
| 30 | Sales |
1. INNER JOIN
INNER JOIN 只返回两个表中匹配的行。
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
结果:
| name | department_name |
|---|---|
| Alice | HR |
| David | HR |
| Bob | Engineering |
2. LEFT JOIN (或 LEFT OUTER JOIN)
LEFT JOIN 返回左表中的所有行以及右表中匹配的行。如果没有匹配，右表中的字段将为 NULL。
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
结果:
| name | department_name |
|---|---|
| Alice | HR |
| Bob | Engineering |
| Charlie | NULL |
| David | HR |
3. RIGHT JOIN (或 RIGHT OUTER JOIN)
RIGHT JOIN 返回右表中的所有行以及左表中匹配的行。如果没有匹配，左表中的字段将为 NULL。
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
结果:
| name | department_name |
|---|---|
| Alice | HR |
| David | HR |
| Bob | Engineering |
| NULL | Sales |
4. FULL JOIN (或 FULL OUTER JOIN)
FULL JOIN 返回两个表中的所有行，并在没有匹配时显示 NULL。
SELECT e.name, d.department_name
FROM employees e
FULL JOIN departments d ON e.department_id = d.department_id;
结果:
| name | department_name |
|---|---|
| Alice | HR |
| Bob | Engineering |
| Charlie | NULL |
| David | HR |
| NULL | Sales |
5. CROSS JOIN
CROSS JOIN 返回两个表的笛卡尔积，即每个来自第一张表的行都会和第二张表中的每一行进行组合。
SELECT e.name, d.department_name
FROM employees e
CROSS JOIN departments d;
结果:
| name | department_name |
|---|---|
| Alice | HR |
| Alice | Engineering |
| Alice | Sales |
| Bob | HR |
| Bob | Engineering |
| Bob | Sales |
| Charlie | HR |
| Charlie | Engineering |
| Charlie | Sales |
| David | HR |
| David | Engineering |
| David | Sales |
6. SELF JOIN
SELF JOIN 是表与自身的连接。假设我们要找到具有相同部门的员工。
SELECT e1.name AS employee1, e2.name AS employee2, e1.department_id
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id
WHERE e1.employee_id <> e2.employee_id;
结果:
| employee1 | employee2 | department_id |
|---|---|---|
| Alice | David | 10 |
| David | Alice | 10 |
每种JOIN都有其特定的用途，选择哪种JOIN取决于你希望如何合并表中的数据。
总结
| Join 类型 | 语法 | 使用场景 |
|---|---|---|
| INNER JOIN | SELECT … FROM table1 INNER JOIN table2 ON table1.column = table2.column; | 当需要从两个表中获取匹配的行时。只返回两个表中都有匹配记录的行。 |
| LEFT JOIN (或 LEFT OUTER JOIN) | SELECT … FROM table1 LEFT JOIN table2 ON table1.column = table2.column; | 当需要从左表中获取所有行，并获取与右表匹配的行时。如果右表中没有匹配记录，则结果中右表的字段为NULL。 |
| RIGHT JOIN (或 RIGHT OUTER JOIN) | SELECT … FROM table1 RIGHT JOIN table2 ON table1.column = table2.column; | 当需要从右表中获取所有行，并获取与左表匹配的行时。如果左表中没有匹配记录，则结果中左表的字段为NULL。 |
| FULL JOIN (或 FULL OUTER JOIN) | SELECT … FROM table1 FULL JOIN table2 ON table1.column = table2.column; | 当需要从两个表中获取所有行，并获取它们之间匹配的行时。如果没有匹配记录，则结果中相应表的字段为NULL。 |
| CROSS JOIN | SELECT … FROM table1 CROSS JOIN table2; | 当需要生成两个表的笛卡尔积时，即每个来自第一张表的行都会和第二张表中的每一行进行组合。通常用于生成测试数据或进行某些特殊计算。 |
| SELF JOIN | SELECT … FROM table1 AS alias1 JOIN table1 AS alias2 ON alias1.column = alias2.column; | 当需要对同一个表进行连接操作时，通常用于查找表内的相关记录。例如，查找具有相同部门或相同兴趣的员工。 |
上述总结仅提供了基本的Join类型和语法示例。在实际使用中，可能还需要根据具体需求添加其他条件、过滤或排序等操作。此外，不同的数据库管理系统（DBMS）可能对Join操作有一些特殊的实现或优化，具体使用时，建议参考相应文档。