MySql中JOIN關(guān)鍵字詳細使用

更新時間：2025年12月04日 16:28:11 作者：b***910

文章介紹了SQL中的四種連接類型：INNERJOIN、LEFTJOIN、RIGHTJOIN和FULLJOIN,并通過圖形化示例展示了它們的區(qū)別,本文結(jié)合實例代碼給大家介紹的非常詳細,感興趣的朋友跟隨小編一起看看吧

INNER JOIN（內(nèi)連接）

返回兩個表中匹配的行。

表達方式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 |
+----+-------+-----+-------+--------+--------+

到此這篇關(guān)于MySql中JOIN關(guān)鍵字詳細使用的文章就介紹到這了,更多相關(guān)mysql json使用內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: