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

这篇具有很好参考价值的文章主要介绍了MySQL多表查询内连接外连接详解,join、left join、right join、full join的使用。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

目录

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);
  • 数据如图所示:
  • leftjoin多表联合查询,MySQL,mysql,数据库,java

 <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

结果:

leftjoin多表联合查询,MySQL,mysql,数据库,java

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

 结果:

leftjoin多表联合查询,MySQL,mysql,数据库,java

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

结果:

leftjoin多表联合查询,MySQL,mysql,数据库,java

 <2>union  all 语句

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

结果:

leftjoin多表联合查询,MySQL,mysql,数据库,java

注意:

  •  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
    )

结果:

leftjoin多表联合查询,MySQL,mysql,数据库,java

2.7、内外连接面试基础

leftjoin多表联合查询,MySQL,mysql,数据库,java

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

  • A:看作是学生表
  • B:看作是班级表

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


2.8、SQL99多表查询新特性

<1>natural  join

  •  自然连接:就是等值(内)连接,会自动查询两张连接表中所有相同的字段,然后进行等值连接

如:上面的内连接SQL为:

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

使用natural  join进行改造,如下:

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

结果:

leftjoin多表联合查询,MySQL,mysql,数据库,java

查询到了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为:

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

使用using:

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

结果:

leftjoin多表联合查询,MySQL,mysql,数据库,java文章来源地址https://www.toymoban.com/news/detail-743698.html

到了这里,关于MySQL多表查询内连接外连接详解,join、left join、right join、full join的使用的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 业务数据LEFT JOIN 多表查询慢--优化操作

    首先你会想到,给表加索引,那么mysql会给主键自动建立索引吗? 会的,当然会。 在我们查询的业务表操作的时候,表业务数据庞大起来的时候,以及left join多的时候,甚至多表关联到几十张表的时候,查询是慢到不行。 这时候,只需要给表join查询的字段,及表结构,进行索

    2024年02月02日
    浏览(42)
  • 关于Mysql使用left join写查询语句执行很慢的问题解决

    目录 (一)前言 (二)正文 1. 表结构/索引展示 (1)表结构 (2)各表索引情况 2. 存在性能问题的SQL语句 3. 解决思路 (1)执行计划思路调优 (2)字符集匹配调优 (三)总结 1. 关于执行计划中TYPE的性能比较 2. 关于left join优化 3. 其他注意点 这几天供应商在测试环境上使

    2024年02月02日
    浏览(49)
  • MySQL Inner Join 和 Left Join 详解

    Inner Join Inner Join 是一种 SQL 查询语句,用于在两个或多个表中查找共有的行。Inner Join 的语法如下: SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; 其中, table1 和 table2 是需要连接的表, column_name 是需要返回的列名。 ON 用于指定连接条件。

    2024年02月05日
    浏览(50)
  • MySQL基础篇补充 | 多表查询中使用SQL99实现7种JOIN操作、SQL99语法新特性

    目录 一:多表查询中使用SQL99实现7种JOIN操作  二:SQL99语法新特性 1. 自然连接Natural 2. USING连接 在多表查询中,除了遇到最多的内连接、左外连接和右外连接,还有其它的连接方式;接下来就聊聊其它的连接方式,如下图:  ​​​​​​ 并且在正式讲解之前,需要先了解

    2024年02月03日
    浏览(46)
  • 【SQL开发实战技巧】系列(三十五):数仓报表场景☞根据条件返回不同列的数据以及Left /Full Join注意事项

    【SQL开发实战技巧】系列(一):关于SQL不得不说的那些事 【SQL开发实战技巧】系列(二):简单单表查询 【SQL开发实战技巧】系列(三):SQL排序的那些事 【SQL开发实战技巧】系列(四):从执行计划讨论UNION ALL与空字符串UNION与OR的使用注意事项 【SQL开发实战技巧】系列

    2023年04月12日
    浏览(64)
  • 连接查询(多表查询)——MySQL

    又称多表查询,当 查询的字段涉及多个表 的时候,就要用到连接查询 分类: 为表起别名: 提高语句的简洁度 区分多个重名字段 注意 :如果为表起了别名,则查询的字段就 不能使用原来的别名去限定 查询A、B 交集部分数据 语法: 隐式内连接 select 字段列表 from 表1,表

    2024年02月04日
    浏览(49)
  • MySQL ——多表连接查询

    内连接 : 假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来。A和B两张表没有主付之分,两张表是平等的。 :inner join on 语句:select * from a_table a inner join b_table b on a.a_id = b.b_id; 说明:组合两个表中的记录,返回关联字段相符的记录,也就

    2024年04月16日
    浏览(55)
  • MySQL left join 和 left outer join 区别

    先说结论: left join 和 left outer join 的结果是一致的。 我不知道各位大神是怎么测试的,网上面就说两个不一样,我A、B表都是有重复数据的,为啥结果是一样的。 表A 表B 左连接 左外连接 以下MySQL官方文档的说明(2664页),outer join是为了某些第三方程序的兼容性而存在的。 微软

    2024年02月10日
    浏览(41)
  • SQL联表查询LEFT JOIN 数据去重复

    使用left join联表查询时,如果table1中的一条记录对应了table2的多条记录,则会重复查出id相同的多条记录。 解决方法: 将查询结果作为中间表,使用group by 进行去重 如果想对group by后的数据计算count,可以将查询结果作为中间表再计算count

    2024年02月11日
    浏览(66)
  • MySQL每日一练:多表查询——连接查询、子查询

    目录 1、首先创建员工表emp和部门表dept: dept表: emp表:  2、插入数据: dept表: emp表: 3、 按条件查找 dept表: emp表:  dept表: emp表: 1.找出销售部门中年纪最大的员工的姓名  2.求财务部门最低工资的员工姓名  3.列出每个部门收入总和高于8000的部门名称   4.求工资在

    2024年02月15日
    浏览(38)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包