MySQL多表查询内连接外连接详解，join、left join、right join、full join的使用

朴灿烈づ我的快乐病毒、 2023-09-25 23:08 157阅读 0赞

**目录**

1、多表查询概览

1.1、分类

1.2、外连接的分类

1.3、常用的SQL语法标准

2、内外联接案例

2.1、初始化表

2.2、内连接

2.3、外连接案例

2.4、全连接案例

2.5、union和union all

2.6、实现MySQL全连接

2.7、内外连接面试基础

2.8、SQL99多表查询新特性

--------------------

### 1、多表查询概览 ###

#### 1.1、分类 ####

可以根据3个角度进行分类：

角度1：是否使用"**=**"符号

*  等值接连：where条件中，表字段与表字段直接使用等于符号（"**=**"）进行判断
 *  非等值连接：where条件中，表字段与表字段使用非"="符号，如：<=（小于等于）、>=（大于等于）、between and等等。

角度2：连接表的数量是否大于1

*  自连接：一张表直接的关联查询，自己表连接自己进行查询，如菜单表查子级
 *  非自连接：多表关联查询

角度3：多表关联时，是否**只**查询有关联的数据

*  内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
 *  外连接：合并具有同一列的两个以上的表的行，结果集中包含一个表与另一个表匹配的行之外，**还包含**了左表 或 右表不匹配的行

#### 1.2、外连接的分类 ####

*  左外连接（**left outer join，可缩写为left join**）：两个表连接过程中，除了返回满足条件的行以外，还会返回**左表中不满足条件的行**，这种连接称为左连接
 *  右外连接（**right outer join，可缩写为right join**）：两个表连接过程中，除了返回满足条件的行以外，还会返回**右表中不满足条件的行**，这种连接称为右连接
 *  全连接（**full outer join，可缩写为full join**）：又称为"满外连接"，两个表连接过程中，返回两表直接的所有数据，这种连接称为全连接

#### 1.3、常用的SQL语法标准 ####

*  SQL92：1992发布的是数据库的一个ANSI/ISO标准（偶尔使用）
 *  SQL99：1999发布的是数据库的一个ANSI/ISO标准（现在开发中主流标准）

1.  ANSI：美国国家标准学会
2.  ISO：国际标准化组织

### 2、内外联接案例 ###

#### 2.1、初始化表 ####

**<1>学生表：student**

create table if not exists taobao.student
    (
    	id int auto_increment primary key,
    	name varchar(50) null,
    	classid int null,
    	age int null
    )
    comment '学生表';
    
    INSERT INTO student (id, name, classid, age) VALUES (1, '张三', 1, 18);
    INSERT INTO student (id, name, classid, age) VALUES (2, '李四', 1, 18);
    INSERT INTO student (id, name, classid, age) VALUES (3, '王五', 2, 17);
    INSERT INTO student (id, name, classid, age) VALUES (4, '老六', 2, 18);
    INSERT INTO student (id, name, classid, age) VALUES (5, '七七', null, 17);
    INSERT INTO student (id, name, classid, age) VALUES (6, '二流子', null, 19);
    INSERT INTO student (id, name, classid, age) VALUES (7, '巴哥', null, 18);

*  数据如图所示：
 *  ![536601205802435da216a4dde1369788.png][]

**<2>班级表：classinfo**

create table if not exists taobao.classinfo
    (
    	classid int auto_increment primary key,
    	name varchar(100) null
    )
    comment '班级表';
    
    INSERT INTO classinfo (name) VALUES ('高一1班');
    INSERT INTO classinfo (name) VALUES ('高一2班');
    INSERT INTO classinfo (name) VALUES ('高一3班');

#### 2.2、内连接 ####

**需求：查询已分配的学生信息，如：学生基本信息，所在班级名称**

**<1>SQL92内连接写法：**

select
        t1.id       -- 学生ID
        ,t1.name    -- 学生姓名
        ,t1.age     -- 学生年龄
        ,t2.name    -- 班级名称
    from student t1,classinfo t2
    where t1.classid=t2.classid

**<2>SQL99内连接写法：**

select
        t1.id       -- 学生ID
        ,t1.name    -- 学生姓名
        ,t1.age     -- 学生年龄
        ,t2.name    -- 班级名称
    from student t1
    join classinfo t2
    on t1.classid=t2.classid

结果：

![e91cadc4b4e146f0a63c5b6275a89390.png][]

#### 2.3、外连接案例 ####

**需求：查询所有的学生信息，并查出学生所对应的班级名称**

**【注意：多表查询时，当查询一个表所有数据，该查询语句一定是外连接】**

**<1>SQL92外连接写法：**

注意：

*  这种写法MySQL不支持，但在Oracle中支持
 *  在不需要查询表中所有数据的那张表后，添加”**(+)**”，表示外连接（理解为这个表只是附加的）

>     select
>         t1.id       -- 学生ID
>          ,t1.name    -- 学生姓名
>          ,t1.age     -- 学生年龄
>          ,t2.name    -- 班级名称
>     from student t1,classinfo t2
>     where t1.classid=t2.classid(+)

**<2>SQL99外连接写法：**

**左连接写法：**

select
        t1.id       -- 学生ID
         ,t1.name    -- 学生姓名
         ,t1.age     -- 学生年龄
         ,t2.name    -- 班级名称
    from student t1
    left join classinfo t2    --注意：left join是缩写，也可以写为：left outer join
    on t1.classid=t2.classid

**右连接写法：**

select
        t2.id       -- 学生ID
        ,t2.name    -- 学生姓名
        ,t2.age     -- 学生年龄
        ,t1.name    -- 班级名称
    from classinfo t1
    right join student t2
    on t1.classid=t2.classid

结果：

![265e9b138d2541328ea59674cc3274c8.png][]

#### 2.4、全连接案例 ####

**需求：查询学生表中的所有信息，并关联班级表信息及显示未关联的班级表信息**

*  在SQL92中，并不直接支持全连接语法

**SQL99全连接写法（Oracle）：**

**关键字：full join ... on ... 或者 full outer join ... on ...**

**注意：MySQL不支持全连接，但是Oracle支持**

select
        t1.id       -- 学生ID
         ,t1.name    -- 学生姓名
         ,t1.age     -- 学生年龄
         ,t2.name    -- 班级名称
    from student t1
    full join classinfo t2
    on t1.classid=t2.classid

MySQL实现全连接，需要使用关键字"union"或者"union all"

#### 2.5、union和union all ####

union：联合、合并的意思

*  union：对两个查询的结果集，进行合并操作，会对重复的数据进行去重，同时进行默认规则（主键升序）的排序（因此效率比较低）。
 *  union all：对两个查询的结果集，进行合并操作，不对数据进行去重，也不进行排序，直接把两个结果进行合并（效率高）。

例如：我们把学生表查询两次，并使用union或union all进行合并

**<1>union 语句**

select * from student
    union   -- 会进行去重操作
    select * from student

结果：

![0f5e5f94d4684de38332b11b6129f247.png][]

**<2>union all 语句**

select * from student
    union all  -- 不去重
    select * from student

结果：

![b116f09c308f439982abfb819ced0c89.png][]

**注意：**

*  union和union all使用时，select下的字段**数量**必须一致，否则会报错

#### 2.6、实现MySQL全连接 ####

**需求：查询学生表中的所有信息，并关联班级表信息及显示未关联的班级表信息**

实现方式有多种，这里我使用：

1.  首先查询出学生表所有信息并显示对应的班级表信息
2.  其次查询班级表中，classid不在学生表中的数据
3.  把上述结果使用union all合并

代码如下：

select
        t1.id       -- 学生ID
         ,t1.name    -- 学生姓名
         ,t1.age     -- 学生年龄
         ,t2.name    -- 班级名称
    from student t1
    left join classinfo t2    -- 注意：left join是缩写，也可以写为：left outer join
    on t1.classid=t2.classid
    union all
    select
        null        -- null：这里设置为null，只是为了与上一个select的结果行字段（数量）进行匹配，以下2个null作用一样
        ,null
        ,null
        ,t1.name
    from classinfo t1
    where t1.classid not in (
        select
            distinct classid    -- distinct表示去重
        from student t2 where t2.classid is not null
        )

结果：

![b831367f79e849df99da3328bcc3aaef.png][]

#### 2.7、内外连接面试基础 ####

![94ff61153eae46ac90820a231b7ae6bc.png][]

上述图对应7种多表查询，是面试及实际开发中，必会的操作，这里就不多言了

*  A：看作是学生表
 *  B：看作是班级表

**注意：当关联表的数量超过3个时，禁止使用join，因为一个join相当于一个for，性能会很差**

--------------------

#### 2.8、SQL99多表查询新特性 ####

**<1>natural join**

*  自然连接：就是等值（内）连接，会自动查询两张连接表中所有相同的字段，然后进行等值连接

如：上面的内连接SQL为：

使用**natural join**进行改造，如下：

select
        t1.id       -- 学生ID
         ,t1.name    -- 学生姓名
         ,t1.age     -- 学生年龄
         ,t2.name    -- 班级名称
    from student t1
    natural join classinfo t2    --自然连接

结果：

![67bf2fe60ed14b798cb2db7c9a33386e.png][]

查询到了0条数据，这是因为：

*  natural join 关联多张表时，会自动根据表中相同的字段名称去匹配
 *  上述student表中classid（班级编号）、name（学生姓名）与classinfo表中的 classid（班级编号）、name（班级名称）是一样的字段，而班级名称不可能与学生姓名相等，所以查询不到数据

也就是上述的自然连接，转义为内连接的SQL为：

select
        t1.id       -- 学生ID
         ,t1.name    -- 学生姓名
         ,t1.age     -- 学生年龄
         ,t2.name    -- 班级名称
    from student t1
    join classinfo t2
    on t1.classid=t2.classid
    and t1.name = t2.name    -- 这个条件也被自然连接附带上了

因此，使用natural join的**前提条件**就是：

1.  **多表关联时，关联字段名称必须相同**
2.  **不进行关联的字段名称必须不相同**

**综上：在实际开发中，我们应当避免使用natural join，造成表与表之间的耦合较高**

**<2>using**

*  等值条件的一种优化写法

> 语法：
> 
>  *  **using(多表关联的字段名称)**
> 
> 前提：
> 
>  *  **多表关联时，关联字段的名称必须相同**
>  *  **注意：这种写法公司一般不给使用，当某个字段改名时，很难定位错误**

如：上面的内连接SQL为：

使用using:

select
        t1.id       -- 学生ID
         ,t1.name    -- 学生姓名
         ,t1.age     -- 学生年龄
         ,t2.name    -- 班级名称
    from student t1
    join classinfo t2
    using(classid)

结果：

![b3e16ffedf18476c84adfbeaeaccb0f4.png][]

[536601205802435da216a4dde1369788.png]: https://img-blog.csdnimg.cn/536601205802435da216a4dde1369788.png
[e91cadc4b4e146f0a63c5b6275a89390.png]: https://img-blog.csdnimg.cn/e91cadc4b4e146f0a63c5b6275a89390.png
[265e9b138d2541328ea59674cc3274c8.png]: https://img-blog.csdnimg.cn/265e9b138d2541328ea59674cc3274c8.png
[0f5e5f94d4684de38332b11b6129f247.png]: https://img-blog.csdnimg.cn/0f5e5f94d4684de38332b11b6129f247.png
[b116f09c308f439982abfb819ced0c89.png]: https://img-blog.csdnimg.cn/b116f09c308f439982abfb819ced0c89.png
[b831367f79e849df99da3328bcc3aaef.png]: https://img-blog.csdnimg.cn/b831367f79e849df99da3328bcc3aaef.png
[94ff61153eae46ac90820a231b7ae6bc.png]: https://img-blog.csdnimg.cn/94ff61153eae46ac90820a231b7ae6bc.png
[67bf2fe60ed14b798cb2db7c9a33386e.png]: https://img-blog.csdnimg.cn/67bf2fe60ed14b798cb2db7c9a33386e.png
[b3e16ffedf18476c84adfbeaeaccb0f4.png]: https://img-blog.csdnimg.cn/b3e16ffedf18476c84adfbeaeaccb0f4.png