mysql 数据操作 多表查询 多表连接查询 全外连接

 

 

全外连接：显示左右两个表全部记录

 

全外连接 在内连接的基础上保留左右两表没有对应关系的记录

 

full join

 

#注意：mysql不支持全外连接 full JOIN

mysql> select * from employee full join department on employee.dep_id = department.id;
ERROR 1054 (42S22): Unknown column 'employee.dep_id' in 'on clause'

 

 

#强调：mysql可以使用此种方式间接实现全外连接

mysql> select * from employee left join department on employee.dep_id = department.id union 
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 | 技术         |
|    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         |
| NULL | NULL       | NULL   | NULL |   NULL |  203 | 运营         |
+------+------------+--------+------+--------+------+--------------+
7 rows in set (0.14 sec)