目录
INNER JOIN（内连接）
返回两个表中匹配的行。
表达方式1：
SELECT *
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
表达方式2：
SELECT *
FROM table1, table2
WHERE table1.column = table2.column;
LEFT JOIN（左连接）
返回左表中的所有行以及与右表匹配的行。
表达方式1：
SELECT *
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
表达方式2：
SELECT *
FROM table1
LEFT OUTER JOIN table2
ON table1.column = table2.column;
RIGHT JOIN（右连接）
返回右表中的所有行以及与左表匹配的行。
表达方式1：
SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
表达方式2：
SELECT *
FROM table1
RIGHT OUTER JOIN table2
ON table1.column = table2.column;
FULL JOIN（全连接）
返回左右表中的所有行。
SELECT *
FROM table1
FULL JOIN table2
ON table1.column = table2.column;
示例图形化解释JOIN的不同类型
INNER JOIN
+----+-------+-----+-------+
| ID | Name | Age | Grade |
+----+-------+-----+-------+
| 1 | John | 24 | A |
| 2 | Alice | 22 | B |
| 3 | Bob | 25 | A |
+----+-------+-----+-------+
+--------+--------+
| ID | City |
+--------+--------+
| 1 | London |
| 2 | Paris |
| 4 | Berlin |
+--------+--------+
After INNER JOIN:
+----+-------+-----+-------+--------+--------+
| ID | Name | Age | Grade | ID | City |
+----+-------+-----+-------+--------+--------+
| 1 | John | 24 | A | 1 | London |
| 2 | Alice | 22 | B | 2 | Paris |
+----+-------+-----+-------+--------+--------+
LEFT JOIN
+----+-------+-----+-------+
| ID | Name | Age | Grade |
+----+-------+-----+-------+
| 1 | John | 24 | A |
| 2 | Alice | 22 | B |
| 3 | Bob | 25 | A |
+----+-------+-----+-------+
+--------+--------+
| ID | City |
+--------+--------+
| 1 | London |
| 2 | Paris |
| 4 | Berlin |
+--------+--------+
After LEFT JOIN:
+----+-------+-----+-------+--------+--------+
| ID | Name | Age | Grade | ID | City |
+----+-------+-----+-------+--------+--------+
| 1 | John | 24 | A | 1 | London |
| 2 | Alice | 22 | B | 2 | Paris |
| 3 | Bob | 25 | A | NULL | NULL |
+----+-------+-----+-------+--------+--------+
RIGHT JOIN
+----+-------+-----+-------+
| ID | Name | Age | Grade |
+----+-------+-----+-------+
| 1 | John | 24 | A |
| 2 | Alice | 22 | B |
| 3 | Bob | 25 | A |
+----+-------+-----+-------+
+--------+--------+
| ID | City |
+--------+--------+
| 1 | London |
| 2 | Paris |
| 4 | Berlin |
+--------+--------+
After RIGHT JOIN:
+----+-------+-----+-------+--------+--------+
| ID | Name | Age | Grade | ID | City |
+----+-------+-----+-------+--------+--------+
| 1 | John | 24 | A | 1 | London |
| 2 | Alice | 22 | B | 2 | Paris |
| NULL | NULL | NULL | NULL | 4 | Berlin |
+----+-------+-----+-------+--------+--------+
FULL JOIN
+----+-------+-----+-------+
| ID | Name | Age | Grade |
+----+-------+-----+-------+
| 1 | John | 24 | A |
| 2 | Alice | 22 | B |
| 3 | Bob | 25 | A |
+----+-------+-----+-------+
+--------+--------+
| ID | City |
+--------+--------+
| 1 | London |
| 2 | Paris |
| 4 | Berlin |
+--------+--------+
After FULL JOIN:
+----+-------+-----+-------+--------+--------+
| ID | Name | Age | Grade | ID | City |
+----+-------+-----+-------+--------+--------+
| 1 | John | 24 | A | 1 | London |
| 2 | Alice | 22 | B | 2 | Paris |
| 3 | Bob | 25 | A | NULL | NULL |
| NULL | NULL | NULL | NULL | 4 | Berlin |
+----+-------+-----+-------+--------+--------+