内连接(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;
假设我们有两个表:employees
和 departments
employees
表:
id | name | department_id |
---|---|---|
1 | Alice | 1 |
2 | Bob | 2 |
3 | Charlie | NULL |
4 | David | 2 |
5 | Eve | 3 |
departments
表:
id | name |
---|---|
1 | HR |
2 | Engineering |
3 | Sales |
4 | Marketing |
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_name | department_name |
---|---|
Alice | HR |
Bob | Engineering |
David | Engineering |
Eve | Sales |
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_name | department_name |
---|---|
Alice | HR |
Bob | Engineering |
Charlie | NULL |
David | Engineering |
Eve | Sales |
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_name | department_name |
---|---|
Alice | HR |
Bob | Engineering |
David | Engineering |
Eve | Sales |
NULL | Marketing |
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_name | department_name |
---|---|
Alice | HR |
Bob | Engineering |
Charlie | NULL |
David | Engineering |
Eve | Sales |
NULL | Marketing |
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
表:
id | name | department_id | manager_id |
---|---|---|---|
1 | Alice | 1 | NULL |
2 | Bob | 2 | 1 |
3 | Charlie | NULL | 1 |
4 | David | 2 | 2 |
5 | Eve | 3 | 2 |
SELECT e1.name AS employee, e2.name AS manager FROM employees e1 INNER JOIN employees e2 ON e1.manager_id = e2.id;
结果:
employee | manager |
---|---|
Bob | Alice |
Charlie | Alice |
David | Bob |
Eve | Bob |
6. 交叉连接(CROSS JOIN)
SELECT employees.name AS employee_name, departments.name AS department_name FROM employees CROSS JOIN departments;
结果:
employee_name | department_name |
---|---|
Alice | HR |
Alice | Engineering |
Alice | Sales |
Alice | Marketing |
Bob | HR |
Bob | Engineering |
Bob | Sales |
Bob | Marketing |
Charlie | HR |
Charlie | Engineering |
Charlie | Sales |
Charlie | Marketing |
David | HR |
David | Engineering |
David | Sales |
David | Marketing |
Eve | HR |
Eve | Engineering |
Eve | Sales |
Eve | Marketing |
7. 自然连接(NATURAL JOIN)
假设employees
和departments
表有相同的列名department_id
:
SELECT employees.name AS employee_name, departments.name AS department_name FROM employees NATURAL JOIN departments;
结果:
employee_name | department_name |
---|---|
Alice | HR |
Bob | Engineering |
David | Engineering |
Eve | Sales |