学SQL JOINS看这一篇文章就够了

Eucalyptus2023-12-04

在数据库中,连接 Join 是用于将两个或多个表中的数据关联起来的操作。连接操作有多种类型,其中包括内连接和外连接。内连接的话只有一种而外连接的话有六种,当然还有一种连接叫做交叉连接。

内连接:join , inner join
外连接:left join , left outer join , right join , right outer join , union
交叉连接:cross join

下面以实例进行分析

#首先创建两张表a和b
mysql> show create table a\G
* 1. row *
Table: a
Create Table: CREATE TABLE a (
id int NOT NULL,
name varchar(20) DEFAULT NULL,
PRIMARY KEY ( id )
) ENGINE InnoDB DEFAULT CHARSET utf8mb3
1 row in set (0.01 sec)

mysql> show create table b\G
* 1. row *
Table: b
Create Table: CREATE TABLE b (
id int NOT NULL,
age int DEFAULT NULL,
PRIMARY KEY ( id )
) ENGINE InnoDB DEFAULT CHARSET utf8mb3
1 row in set (0.01 sec)

两张表格式如下:

内连接

inner join 或者join 等同于inner join

语法:

1
2
3

select * from a join b on a.id b.id;
or
select * from a inner join b on a.id b.id;

结果:

应用场景:

这种场景下得到的是满足某一条件的A,B内部的数据;正因为得到的是内部共有数据,所以连接方式称为内连接。

外连接

left join 或者left outer join(等同于left join)

语法:

1
2
3

select * from a left join b on a.id b.id;
or
select * from a left outer join b on a.id b.id;

结果:

结果如下,TableA中B不存在的记录填充Null

应用场景:

这种场景下得到的是A的所有数据,和满足某一条件的B的数据。

[ left join 或者left outer join(等同于left join) ] [ where B.column is null ]

语法:

1	select * from a left outer join b on a.id b.id where b.id is not null;

结果:

left join表a的数据全部显示,匹配表b的数据也显示,而b.id再次过滤掉表b的id为空的

应用场景:

这种场景下得到的是A中的所有数据减去与B满足同一条件的数据 ,然后得到的A剩余数据。

right join 或者right outer join(等同于right join)

语法:

1
2
3

select * from a right outer join b on a.id b.id;
or
select * from a right outer join b on a.id b.id;

结果:

TableB中A不存在的记录填充Null

应用场景:

这种场景下得到的是B的所有数据,和满足某一条件的A的数据。

[ right join 或者right outer join(等同于right join) ] [ where A.column is null ]

语法:

1	select * from a right join b on a.id b.id where a.id is not null;

结果:

应用场景:

这种场景下得到的是B中的所有数据减去与A满足同一条件的数据“,然后得到的B剩余数据。

full join mysql不支持,但是可以用 left join union right join代替

语法:

1	select * from a left join b on a.id b.id union select * from a right join b on a.id b.id;

结果:

union过后,重复的记录会合并 id为2,3,4的三条记录

应用场景:

这种场景下得到的是满足某一条件的公共记录,和独有的记录。

full join is null mysql不支持,但是可以用 left join is null union right join is null代替

语法:

1	select * from a left join b on a.id b.id where b.id is null union select * from a right join b on a.id b.id where a.id is null;

结果:

应用场景:

交叉连接

cross join

语法:

1	select * from a cross join b;

结果:

cross join where

语法:

1	select * from a cross join b where a.id b.id;

结果:

这种情况下实际上实现了内连接的效果

注意事项

一般cross join后面加上where条件,但是用cross join on也是被解释为cross join where 显示连接 ;
一般内连接都需要加上on限定条件,如果不加会被解释为交叉连接;
如果连接表格使用的是逗号,会被解释为交叉连接隐式连接。

注:隐式连接 Implicit Join 会出现数据冗余和性能问题,主要是因为它使用逗号分隔表名的方式进行连接,而没有明确指定连接条件,这样就会导致以下两个问题:

数据冗余问题: 隐式连接会产生笛卡尔积,即将左表的每一行与右表的每一行进行组合,然后返回所有可能的组合结果,包括那些并不符合连接条件的组合。这就会导致结果集中出现多余的数据,从而产生数据冗余的问题。
性能问题: 隐式连接的查询效率很低,因为它会处理大量的无用数据,浪费了系统资源和时间。当表a和表b的记录数都很大时,隐式连接会产生庞大的临时表,消耗大量内存和CPU资源。此外,由于没有明确指定连接条件,数据库引擎会对每一个可能组合进行比较,这会进一步降低查询效率。