mysql 数据操作多表查询多表连接查询笛卡尔积

mingerlcm 2021-10-14 原文

1 交叉连接：不适用任何匹配条件。生成笛卡尔积

所有员工都和四个部门做了对应关系

mysql> select * from employee,department;
+----+------------+--------+------+--------+------+--------------+
| id | name       | sex    | age  | dep_id | id   | name         |
+----+------------+--------+------+--------+------+--------------+
|  1 | mike       | male   |   18 |    200 |  200 | 技术         |
|  1 | mike       | male   |   18 |    200 |  201 | 人力资源     |
|  1 | mike       | male   |   18 |    200 |  202 | 销售         |
|  1 | mike       | male   |   18 |    200 |  203 | 运营         |
|  2 | alex       | female |   48 |    201 |  200 | 技术         |
|  2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|  2 | alex       | female |   48 |    201 |  202 | 销售         |
|  2 | alex       | female |   48 |    201 |  203 | 运营         |
|  3 | jack       | male   |   38 |    201 |  200 | 技术         |
|  3 | jack       | male   |   38 |    201 |  201 | 人力资源     |
|  3 | jack       | male   |   38 |    201 |  202 | 销售         |
|  3 | jack       | male   |   38 |    201 |  203 | 运营         |
|  4 | yuanhao    | female |   28 |    202 |  200 | 技术         |
|  4 | yuanhao    | female |   28 |    202 |  201 | 人力资源     |
|  4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|  4 | yuanhao    | female |   28 |    202 |  203 | 运营         |
|  5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|  5 | liwenzhou  | male   |   18 |    200 |  201 | 人力资源     |
|  5 | liwenzhou  | male   |   18 |    200 |  202 | 销售         |
|  5 | liwenzhou  | male   |   18 |    200 |  203 | 运营         |
|  6 | jingliyang | female |   18 |    204 |  200 | 技术         |
|  6 | jingliyang | female |   18 |    204 |  201 | 人力资源     |
|  6 | jingliyang | female |   18 |    204 |  202 | 销售         |
|  6 | jingliyang | female |   18 |    204 |  203 | 运营         |
+----+------------+--------+------+--------+------+--------------+
24 rows in set (0.00 sec)

有两张表一张左表一张右表左表所有记录都会跟右表对应一遍

优点：简单粗暴两张表对上

缺点：有些记录没有意义没用

在笛卡尔积基础上筛选有关系的加上限制关系

员工表的employee.dep_id == 部门表department.id 对上才是有关联的记录

加上where 过滤掉没有意义的记录取出那些有关联关系的记录

库名.表名.字段名

mysql> select * from employee,department where  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 | 技术         |
+----+-----------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)