mysql的内连接,左连接和右链接查询详解

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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（内连接,或等值连接）：

使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)来连接以上两张表

?

1 2 3

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;

?

1 2 3 4 5 6 7 8

+ -------------+-----------------+----------------+ | 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 会读取左边数据表的全部数据，即便右边表无对应数据。

?

1	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;

?

1 2 3 4 5 6 7 8 9

+ -------------+-----------------+----------------+ | 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 会读取右边数据表的全部数据，即便左边边表无对应数据。

?

1	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;

?

1 2 3 4 5 6 7 8 9

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

总结

本篇文章就到这里了，希望能够给你带来帮助，也希望您能够多多关注服务器之家的更多内容!    

原文链接：https://blog.csdn.net/qq_30315977/article/details/123634065