mysql join 原理 简书_MySQL基础-join

Mysql七大连接

前言

数据库作为储存数据的一个载体，我们通常把一些数据存在不同的表中，但是通常我们需要通过查询多张表来查询数据，这个时候我们就需要用到sql里面的连接——join，而join大体上分为内连接和外连接，而外连接可以细分为左连接、右连接和全连接。

基础表

author

id

author_name

1

吴承恩

2

曹雪芹

3

罗贯中

4

施耐庵

5

金庸

book

id

book_name

author_id

1001

西游记

1

1002

红楼梦

2

1003

水浒传

3

1004

三国演义

4

1005

疯狂Java讲义

6

正如上面的表所示，测试用的表分为book表和author表，book表记录了书本名称和作者id，而author表记录了作者的信息。(为了测试方便，表中数据不符合参照完整性规范)

笛卡尔积

笛卡尔积是一种集合运算方法，它是sql中连接的基础

笛卡尔积

形式化公式为：

笛卡尔积形式化公式

SQL中的形式化运算符号：笛卡尔积(×)、连接(∞)、投影(π)、选择(σ)

笛卡尔积(×)与连接(∞)

连接运算是从两个关系的广义笛卡尔积中选取属性间满足一定条件的元组形成一个新关系

投影(π)、选择(σ)

两者的选择是投影>选择

投影是选出某一属性

选择是选出特定的属性

实例

SELECT * FROM author inner join book

# 或 (inner join )可省略

SELECT * FROM author,book

id

author_name

id(1)

book_name

author_id

1

吴承恩

1001

西游记

1

2

曹雪芹

1001

西游记

1

3

罗贯中

1001

西游记

1

4

施耐庵

1001

西游记

1

5

金庸

1001

西游记

1

1

吴承恩

1002

红楼梦

2

2

曹雪芹

1002

红楼梦

2

3

罗贯中

1002

红楼梦

2

4

施耐庵

1002

红楼梦

2

5

金庸

1002

红楼梦

2

1

吴承恩

1003

水浒传

3

2

曹雪芹

1003

水浒传

3

3

罗贯中

1003

水浒传

3

4

施耐庵

1003

水浒传

3

5

金庸

1003

水浒传

3

1

吴承恩

1004

三国演义

4

2

曹雪芹

1004

三国演义

4

3

罗贯中

1004

三国演义

4

4

施耐庵

1004

三国演义

4

5

金庸

1004

三国演义

4

1

吴承恩

1005

疯狂Java讲义

6

2

曹雪芹

1005

疯狂Java讲义

6

3

罗贯中

1005

疯狂Java讲义

6

4

施耐庵

1005

疯狂Java讲义

6

5

金庸

1005

疯狂Java讲义

6

内连接

内连接就是通过一定条件从笛卡尔积中筛选出特定的数据，例如以下示例：

内连接

SELECT * FROM author a,book b WHERE a.id = b.author_id

id

author_name

id(1)

book_name

author_id

1

吴承恩

1001

西游记

1

2

曹雪芹

1002

红楼梦

2

3

罗贯中

1003

水浒传

3

4

施耐庵

1004

三国演义

4

左连接

左全连接

左全连接

SELECT * FROM author a LEFT JOIN book b on b.author_id = a.id

id

author_name

id(1)

book_name

author_id

1

吴承恩

1001

西游记

1

2

曹雪芹

1002

红楼梦

2

3

罗贯中

1003

水浒传

3

4

施耐庵

1004

三国演义

4

5

金庸

Null

Null

Null

左非全连接

左非全连接

SELECT * FROM author a LEFT JOIN book b on b.author_id = a.id WHERE b.id IS NULL

id

author_name

id(1)

book_name

author_id

5

金庸

Null

Null

Null

右连接

右连接其实和左连接一样，如果要使用它的话，你可以选择继续使用left join，然后通过调换表的位置来实现，也可以通过right join来实现。

右全连接

右连接

SELECT * FROM author a RIGHT JOIN book b ON a.id = b.author_id

id

author_name

id(1)

book_name

author_id

1

吴承恩

1001

西游记

1

2

曹雪芹

1002

红楼梦

2

3

罗贯中

1003

水浒传

3

4

施耐庵

1004

三国演义

4

Null

Null

1005

疯狂Java讲义

6

右非全连接

右非全连接

SELECT * FROM author a RIGHT JOIN book b ON a.id = b.author_id WHERE a.id is NULL

id

author_name

id(1)

book_name

author_id

Null

Null

1005

疯狂Java讲义

6

外连接

全连接

注：MySQL不支持全连接，但是还是有方式去完成全连接的功能，比如通过使用left join、right join通过union来合并达到其效果

SELECT * FROM author a LEFT JOIN book b ON a.id = b.author_id

UNION

SELECT * FROM author a RIGHT JOIN book b ON a.id = b.author_id

id

author_name

id(1)

book_name

author_id

1

吴承恩

1001

西游记

1

2

曹雪芹

1002

红楼梦

2

3

罗贯中

1003

水浒传

3

4

施耐庵

1004

三国演义

4

5

金庸

Null

Null

Null

Null

Null

1005

疯狂Java讲义

6

连接

要达到这种查询效果我们依旧可以通过上面的方式，通过添加条件来查询结果。

SELECT * FROM author a LEFT JOIN book b ON a.id = b.author_id WHERE b.id is NULL

UNION

SELECT * FROM author a RIGHT JOIN book b ON a.id = b.author_id WHERE a.id is NULL

id

author_name

id(1)

book_name

author_id

5

金庸

Null

Null

Null

Null

Null

1005

疯狂Java讲义

6

总结

学会了join相当于掌握了许多工具，为了满足我们的需求最重要的是如何灵活的使用这些工具，首先是达到自己的目的，然后再是优化。