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)