介绍
1.INNER JOIN(内连接)
返回两个表中满足连接条件的交集,即只返回匹配的行。
语法:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
2.LEFT JOIN(左连接)
返回左表中的所有行,以及右表中满足连接条件的行。如果右表中没有匹配的行,则结果中包含NULL。
语法:
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
3.RIGHT JOIN(右连接)
返回右表中的所有行,以及左表中满足连接条件的行。如果左表中没有匹配的行,则结果中包含NULL。
语法:
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
4.FULL JOIN(全连接)
返回两个表中的所有行。如果某一表中没有匹配的行,则结果中包含NULL。
语法:
SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;
5.CROSS JOIN(交叉连接)
返回两个表的笛卡尔积,即将每一行与另一表的每一行组合。
语法:
SELECT columns
FROM table1
CROSS JOIN table2;
6.SELF JOIN(自连接)
表示同一个表内部的连接,用于从同一个表中获取相关数据。
语法:
SELECT a.columns, b.columns
FROM table a, table b
WHERE a.common_column = b.common_column;
7.NATURAL JOIN(自然连接)
基于两个表中所有同名的列进行连接。
语法:
SELECT columns
FROM table1
NATURAL JOIN table2;
举例说明
假设有两个表
//`employee`表
id | name | department_id
---|-----------|--------------
1 | Alice | 1
2 | Bob | 2
3 | Charlie | 1
//`department`表
id | department_name
---|----------------
1 | HR
2 | IT
3 | Finance
1)INNER JOIN 示例
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;
结果
name | department_name
--------|----------------
Alice | HR
Bob | IT
Charlie | HR
2)LEFT JOIN 示例
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;
结果
name | department_name
--------|----------------
Alice | HR
Bob | IT
Charlie | HR
3)RIGHT JOIN 示例
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;
结果
name | department_name
--------|----------------
Alice | HR
Bob | IT
NULL | Finance
4)FULL JOIN 示例
SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments
ON employees.department_id = departments.id;
结果
name | department_name
--------|----------------
Alice | HR
Bob | IT
Charlie | HR
NULL | Finance