最近项目中要写个SQL,查询A表和B表都存在的情况下,很显然,用 inner join。
半天没写出来,请别人帮忙之后,问了个很菜鸟的问题,为啥要用inner join 哈哈
所以在这简单总结下:
有如下数据
mysql> select a.stuid,a.stuname,b.stuname from testa a,testb b where a.stuid=b.stuid; +-------+---------+---------+ | stuid | stuname | stuname | +-------+---------+---------+ | 1 | yoyo | koko | | 2 | sasa | tom | | 3 | tom | jerry | | 4 | jerry | yoyo | +-------+---------+---------+ 4 rows in set (0.00 sec)
①inner join:内连接
两个表都存在的记录(红色部分)。
mysql> select * from testa a inner join testb b on a.stuname = b.stuname; +-------+---------+-------+---------+ | stuid | stuname | stuid | stuname | +-------+---------+-------+---------+ | 3 | tom | 2 | tom | | 4 | jerry | 3 | jerry | | 1 | yoyo | 4 | yoyo | +-------+---------+-------+---------+ 3 rows in set (0.00 sec)
②left join:左连接
取得左表全部记录 和 右表匹配的记录,如果没有匹配的显示为null(红色部分)。
mysql> select * from testa a left join testb b on a.stuname = b.stuname; +-------+---------+-------+---------+ | stuid | stuname | stuid | stuname | +-------+---------+-------+---------+ | 1 | yoyo | 4 | yoyo | | 2 | sasa | NULL | NULL | | 3 | tom | 2 | tom | | 4 | jerry | 3 | jerry | +-------+---------+-------+---------+ 4 rows in set (0.00 sec)
③right join:右连接
取得右边全部记录 和 左表匹配的记录,如果没有匹配的显示为null(红色部分)。
mysql> select * from testa a right join testb b on a.stuname = b.stuname; +-------+---------+-------+---------+ | stuid | stuname | stuid | stuname | +-------+---------+-------+---------+ | NULL | NULL | 1 | koko | | 3 | tom | 2 | tom | | 4 | jerry | 3 | jerry | | 1 | yoyo | 4 | yoyo | +-------+---------+-------+---------+ 4 rows in set (0.00 sec)
以上。