快捷導航

mysql的內(nèi)連接,左連接和右鏈接查詢詳解

更新時間：2022年03月22日 15:33:02 作者：博文豆芽菜

這篇文章主要為大家詳細介紹了mysql的內(nèi)連接,左連接和右鏈接查詢，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下，希望能夠給你帶來幫助

mysql> SELECT * FROM tcount_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鳥教程  | 10           |
| RUNOOB.COM    | 20           |
| Google        | 22           |
+---------------+--------------+
3 rows in set (0.01 sec)
 
mysql> SELECT * from runoob_tbl;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1         | 學習 PHP    | 菜鳥教程  | 2017-04-12      |
| 2         | 學習 MySQL  | 菜鳥教程  | 2017-04-12      |
| 3         | 學習 Java   | RUNOOB.COM    | 2015-05-01      |
| 4         | 學習 Python | RUNOOB.COM    | 2016-03-06      |
| 5         | 學習 C      | FK            | 2017-04-05      |
+-----------+---------------+---------------+-----------------+
5 rows in set (0.01 sec)

INNER JOIN（內(nèi)連接,或等值連接）：

使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一樣)來連接以上兩張表

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author；
相當于：
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鳥教程    | 10             |
| 2           | 菜鳥教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
+-------------+-----------------+----------------+

MySQL LEFT JOIN：

MySQL left join 與 join 有所不同。 MySQL LEFT JOIN 會讀取左邊數(shù)據(jù)表的全部數(shù)據(jù)，即便右邊表無對應(yīng)數(shù)據(jù)。

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鳥教程    | 10             |
| 2           | 菜鳥教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
| 5           | FK              | NULL           |
+-------------+-----------------+----------------+

MySQL RIGHT JOIN:

MySQL RIGHT JOIN 會讀取右邊數(shù)據(jù)表的全部數(shù)據(jù)，即便左邊邊表無對應(yīng)數(shù)據(jù)。

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鳥教程    | 10             |
| 2           | 菜鳥教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
| NULL        | NULL            | 22             |
+-------------+-----------------+----------------+