表连接查询——笛卡尔积

创建表结构

create table t2(
id int,
name varchar(20) 
);


insert into t2 values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

create table t3(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);


insert into t3(name,sex,age,dep_id) values
('jack ma','male',18,200),
('alex','female',48,201),
('peiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;

1 内连接

自动过滤掉没有对应上的记录

mysql> select * from t3 inner join t2 on t3.dep_id = t2.id;
+----+-----------+--------+------+--------+------+--------------+
| id | name      | sex    | age  | dep_id | id   | name         |
+----+-----------+--------+------+--------+------+--------------+
|  1 | jack ma   | male   |   18 |    200 |  200 | 技术         |
|  2 | alex      | female |   48 |    201 |  201 | 人力资源     |
|  3 | peiqi     | male   |   38 |    201 |  201 | 人力资源     |
|  4 | yuanhao   | female |   28 |    202 |  202 | 销售         |
|  5 | liwenzhou | male   |   18 |    200 |  200 | 技术         |
+----+-----------+--------+------+--------+------+--------------+

2 左连接

保留左表的没有对应上的记录，null填充

mysql> select * from t3 left join t2 on t3.dep_id = t2.id;
+----+------------+--------+------+--------+------+--------------+
| id | name       | sex    | age  | dep_id | id   | name         |
+----+------------+--------+------+--------+------+--------------+
|  1 | jack ma    | male   |   18 |    200 |  200 | 技术         |
|  5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|  2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|  3 | peiqi      | male   |   38 |    201 |  201 | 人力资源     |
|  4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|  6 | jingliyang | female |   18 |    204 | NULL | NULL         |
+----+------------+--------+------+--------+------+--------------+

3 右连接

保留右表的没有对应上的记录，null填充

mysql> select * from t3 right join t2 on t3.dep_id = t2.id;
+------+-----------+--------+------+--------+------+--------------+
| id   | name      | sex    | age  | dep_id | id   | name         |
+------+-----------+--------+------+--------+------+--------------+
|    1 | jack ma   | male   |   18 |    200 |  200 | 技术         |
|    2 | alex      | female |   48 |    201 |  201 | 人力资源     |
|    3 | peiqi     | male   |   38 |    201 |  201 | 人力资源     |
|    4 | yuanhao   | female |   28 |    202 |  202 | 销售         |
|    5 | liwenzhou | male   |   18 |    200 |  200 | 技术         |
| NULL | NULL      | NULL   | NULL |   NULL |  203 | 运营         |
+------+-----------+--------+------+--------+------+--------------+

4 全外连接

就是左连接和右连接中间加union，好像只能这么写，觉得有点纯。

mysql> select * from t3 right join t2 on t3.dep_id = t2.id
    -> union
    -> select * from t3 left join t2 on t3.dep_id = t2.id;
+------+------------+--------+------+--------+------+--------------+
| id   | name       | sex    | age  | dep_id | id   | name         |
+------+------------+--------+------+--------+------+--------------+
|    1 | jack ma    | male   |   18 |    200 |  200 | 技术         |
|    2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|    3 | peiqi      | male   |   38 |    201 |  201 | 人力资源     |
|    4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|    5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
| NULL | NULL       | NULL   | NULL |   NULL |  203 | 运营         |
|    6 | jingliyang | female |   18 |    204 | NULL | NULL         |
+------+------------+--------+------+--------+------+--------------+