欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql 使用技巧 where条件连接;inner join内连接;外连接(left outer join,right outer join)

程序员文章站 2022-07-10 21:58:24
mysql的inner join等价于where条件连接查询 内连接 inner join 省略形式 join 外连接 左连接 left outer join 省略形式 left join 右连接 right outer join 省略形式 right join 两张表内容: mysql> use ......

mysql的inner join等价于where条件连接查询

内连接 

mysql 使用技巧 where条件连接;inner join内连接;外连接(left outer join,right outer join)

inner join 省略形式  join

 

外连接

mysql 使用技巧 where条件连接;inner join内连接;外连接(left outer join,right outer join)

左连接 left outer join 省略形式 left join 

 

mysql 使用技巧 where条件连接;inner join内连接;外连接(left outer join,right outer join)

右连接 right outer join 省略形式 right join  

两张表内容:

mysql> use runoob;
database changed
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)

接下来我们就使用mysql的inner join(也可以省略 inner 使用 join,效果一样)来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值:

inner join

mysql> select a.runoob_id, a.runoob_author, b.runoob_count from runoob_tbl a inner 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             |
+-------------+-----------------+----------------+
4 rows in set (0.00 sec)

等价于where的条件查询

mysql> select a.runoob_id, a.runoob_author, b.runoob_count from runoob_tbl a, tcount_tbl b where 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             |
+-------------+-----------------+----------------+
4 rows in set (0.01 sec)

mysql 使用技巧 where条件连接;inner join内连接;外连接(left outer join,right outer join)