MySql-连接查询案例分析（图文详解）

1 连接查询图解

 

 

 

2 连接查询示例

2.1 创建示例表

设置两表连接的字段为 book.author_id，author.id。根据连接字段的不同，两表中的最后一行为各自独有的部分。

create table book ( book_id int primary key, book_name varchar(30), author_id int);	create table author ( id int primary key, author_name varchar(30) );
insert into book(book_id,book_name,author_id) values(1,'xiyouji',101) ,(2,'hongloumeng',102) ,(3,'sanguoyanyi',103) ,(4,'shuihuzhuan',106);	insert into author(id,author_name) values (101,'wuchengen') ,(102,'caoxueqin') ,(103,'luoguanzhong') ,(105,'jinyong');
select * from book;	select * from author;
book_id|book_name  |author_id| -------|-----------|---------|       1|xiyouji    |      101|       2|hongloumeng|      102|       3|sanguoyanyi|      103|       4|shuihuzhuan|      106|	id |author_name | ---|------------| 101|wuchengen   | 102|caoxueqin   | 103|luoguanzhong| 105|jinyong     |

 

2.2 交叉连接（无连接条件）

没加连接条件时，是两表的所有行逐一匹配。交叉连接返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积。

select A.*, B.* from book A, author B;

book_id|book_name  |author_id|id |author_name | -------|-----------|---------|---|------------|       1|xiyouji    |      101|101|wuchengen   |       2|hongloumeng|      102|101|wuchengen   |       3|sanguoyanyi|      103|101|wuchengen   |       4|shuihuzhuan|      106|101|wuchengen   |       1|xiyouji    |      101|102|caoxueqin   |       2|hongloumeng|      102|102|caoxueqin   |       3|sanguoyanyi|      103|102|caoxueqin   |       4|shuihuzhuan|      106|102|caoxueqin   |       1|xiyouji    |      101|103|luoguanzhong|       2|hongloumeng|      102|103|luoguanzhong|       3|sanguoyanyi|      103|103|luoguanzhong|       4|shuihuzhuan|      106|103|luoguanzhong|       1|xiyouji    |      101|105|jinyong     |       2|hongloumeng|      102|105|jinyong     |       3|sanguoyanyi|      103|105|jinyong     |       4|shuihuzhuan|      106|105|jinyong     |

 

2.3 内连接

根据连接条件，查询两表的共有部分。

select A.*, B.* from book A join author B on A.author_id = B.id;
book_id|book_name  |author_id|id |author_name | -------|-----------|---------|---|------------|       1|xiyouji    |      101|101|wuchengen   |       2|hongloumeng|      102|102|caoxueqin   |       3|sanguoyanyi|      103|103|luoguanzhong|

 

2.4 左连接

根据连接条件，查询左表的独有部分以及两表的共有部分。

select A.*, B.* from book A left join author B on A.author_id = B.id;
book_id|book_name  |author_id|id  |author_name | -------|-----------|---------|----|------------|       1|xiyouji    |      101|101 |wuchengen   |       2|hongloumeng|      102|102 |caoxueqin   |       3|sanguoyanyi|      103|103 |luoguanzhong|       4|shuihuzhuan|      106|null| null      |

 

 

2.5 左连接+筛选

根据连接以及筛选条件，查询左表的独有部分。

select A.*, B.* from book A left join author B on A.author_id = B.id where B.id is null;
book_id|book_name  |author_id|id  |author_name| -------|-----------|---------|----|-----------|       4|shuihuzhuan|      106|null|   null    |

 

2.6 右连接

根据连接条件，查询右表的独有部分以及两表的共有部分。

select A.*, B.* from book A right join author B on A.author_id = B.id;
book_id|book_name  |author_id|id |author_name | -------|-----------|---------|---|------------|       1|xiyouji    |      101|101|wuchengen   |       2|hongloumeng|      102|102|caoxueqin   |       3|sanguoyanyi|      103|103|luoguanzhong|    null|null       |     null|105|jinyong     |

 

 

2.7 右连接+筛选

根据连接以及筛选条件，查询右表的独有部分。

select A.*, B.* from book A right join author B on A.author_id = B.id where A.author_id is null;
book_id|book_name|author_id|id |author_name| -------|---------|---------|---|-----------|    null|     null|     null|105|jinyong    |

 

 

2.8 全连接

MySQL不支持全连接语法，但可以通过union联合查询左连接以及右连接实现。

根据连接条件，查询左表的独有部分、右表的独有部分、两表的共有部分。

select A.*, B.* from book A left join author B on A.author_id = B.id union  select A.*, B.* from book A right join author B on A.author_id = B.id;
book_id|book_name  |author_id|id  |author_name | -------|-----------|---------|----|------------|       1|xiyouji    |      101|101 |wuchengen   |       2|hongloumeng|      102|102 |caoxueqin   |       3|sanguoyanyi|      103|103 |luoguanzhong|       4|shuihuzhuan|      106|null|null        |    null|null       |     null|105 |jinyong     |

 

2.9 全连接+筛选

可以用左连接+筛选与右连接+筛选联合查询实现，由于组合后不会有重复部分，可以用union all。

根据连接条件，查询左表的独有部分、右表的独有部分。

select A.*, B.* from book A left join author B on A.author_id = B.id where B.id is null union all select A.*, B.* from book A right join author B on A.author_id = B.id where A.author_id is null;
book_id|book_name  |author_id|id  |author_name| -------|-----------|---------|----|-----------|       4|shuihuzhuan|      106|null|null       |    null|null       |     null|105 |jinyong    |