mysql 数据操作 多表查询 多表连接查询 外链接之左连接 右连接
1.外链接之左连接:优先显示左表全部记录
left join
在内连接的基础上保留左表的记录
即便左表有一条记录和右表没有关系,也把他留下
mysql> select * from employee left join department on employee.dep_id = department.id; +----+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+------------+--------+------+--------+------+--------------+ | 1 | mike | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | jack | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | NULL | NULL | +----+------------+--------+------+--------+------+--------------+ 6 rows in set (0.00 sec)
2.外链接之右连接:优先显示右表全部记录
在内连接的基础上保留右表的记录
right join
mysql> select * from employee right join department on employee.dep_id = department.id; +------+-----------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+-----------+--------+------+--------+------+--------------+ | 1 | mike | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | jack | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+-----------+--------+------+--------+------+--------------+ 6 rows in set (0.00 sec)