mysql 连接查询分为大致分为左连接，右连接，内连接，外连接.
mysql join
join.jpg
- table1
mysql> select *from user;
+----+-------+----------+
| id | name | phone_id |
+----+-------+----------+
| 1 | laohe | 1 |
| 2 | wang | 2 |
| 3 | chen | 10 |
| 4 | lisi | 8 |
+----+-------+----------+
4 rows in set (0.00 sec)
- table2
mysql> select *from phone;
+----+--------+
| id | phone |
+----+--------+
| 1 | huawei |
| 2 | xiaomi |
| 3 | oppo |
| 4 | apple |
+----+--------+
4 rows in set (0.00 sec)
左连接 LEFT JOIN
左连接是先根据两张table的公有关键字得到公共部分, 然后将公共部分数据连接到左边table的剩下的数据.
mysql> select * from user left join phone on user.phone_id = phone.id;
+----+-------+----------+------+--------+
| id | name | phone_id | id | phone |
+----+-------+----------+------+--------+
| 1 | laohe | 1 | 1 | huawei |
| 2 | wang | 2 | 2 | xiaomi |
| 3 | chen | 10 | NULL | NULL |
| 4 | lisi | 8 | NULL | NULL |
+----+-------+----------+------+--------+
4 rows in set (0.00 sec)
右连接 RIGHT JOIN
右连接和左连接相反, 是先根据两张table的公有关键字得到公共部分, 然后将公共部分数据连接到右边table的剩下的数据.
mysql> select * from user right join phone on user.phone_id = phone.id;
+------+-------+----------+----+--------+
| id | name | phone_id | id | phone |
+------+-------+----------+----+--------+
| 1 | laohe | 1 | 1 | huawei |
| 2 | wang | 2 | 2 | xiaomi |
| NULL | NULL | NULL | 3 | oppo |
| NULL | NULL | NULL | 4 | apple |
+------+-------+----------+----+--------+
4 rows in set (0.00 sec)
内连接 INNER JOIN
内连接就是常用的根据两个表的共有关键字, 获取两张表的公共数据.
JOIN 和 INNER JOIN 是同样的.
mysql> select * from user inner join phone on user.phone_id = phone.id;
+----+-------+----------+----+--------+
| id | name | phone_id | id | phone |
+----+-------+----------+----+--------+
| 1 | laohe | 1 | 1 | huawei |
| 2 | wang | 2 | 2 | xiaomi |
+----+-------+----------+----+--------+
2 rows in set (0.00 sec)
外连接 OUTER JOIN
外连接就是取两张table的所有数据,连接到一起. 两张表的共有数据分别连接 两张表的剩余(非共有)部分.
mysql 不支持OUTER JOIN，可以对左连接和右连接的结果做UNION操作实现.
mysql> select * from user left join phone on user.phone_id = phone.id union select * from user right join phone on user.phone_id = phone.id;
+------+-------+----------+------+--------+
| id | name | phone_id | id | phone |
+------+-------+----------+------+--------+
| 1 | laohe | 1 | 1 | huawei |
| 2 | wang | 2 | 2 | xiaomi |
| 3 | chen | 10 | NULL | NULL |
| 4 | lisi | 8 | NULL | NULL |
| NULL | NULL | NULL | 3 | oppo |
| NULL | NULL | NULL | 4 | apple |
+------+-------+----------+------+--------+
6 rows in set (0.00 sec)
