inner join 与 left join 之间的区别

@K@

已于 2022-09-12 22:04:41 修改

阅读量2k

点赞数 2

分类专栏： Oracle MySQL 文章标签： sql

于 2022-09-12 21:42:57 首次发布

本文链接：https://blog.csdn.net/weixin_48584982/article/details/126823324

版权

Oracle 同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

MySQL

2 篇文章 0 订阅

订阅专栏

关于inner join 与 left join 之间的区别，网上的例子不胜枚举，我就不一一说了，这只记录一下用法。以防自己哪天忘记。

内连接
inner join(等值连接)：只显示两表联结字段相等的行；

外连接
left join：以左表为基础,显示左表中的所有记录,不管是否与关联条件相匹配,而右表中的数据只显示与关联条件相匹配的记录,不匹配的记录以NULL字符填充；

右连接

right join：以右表为基础,显示左表中的所有记录,不管是否与关联条件相匹配,而左表中的数据只显示与关联条件相匹配的记录,不匹配的记录以NULL字符填充

例：
A表：
id name
1 a1
2 a2
3 a3

B表：
id class
1 b1
2 b2
4 b4
inner join：
select a.id,a.name,b.class from A a inner join B b on a.id = b.id
得出结果：
1 a1 b1
2 a2 b2

left join：
select a.id,a.name,b.class from A a left join B b on a.id = b.id
得出结果：
1 a1 b1
2 a2 b2
3 a3 null

right join：
select a.id,a.name,b.class from A a right join B b on a.id = b.id
得出结果：
1 a1 b1
2 a2 b2
4 null b4

结果说明:

这里只显示出了 A.id = B.id的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.

自己造的数据，感兴趣的可以自己跑一把。

CREATE TABLE `t_notific_info` (
  `id` int NOT NULL,
  `created_time` date DEFAULT NULL,
  `update_time` date DEFAULT NULL,
  `title` varchar(45) DEFAULT NULL,
  `type` varchar(1) DEFAULT NULL,
  `content` varchar(1024) DEFAULT NULL,
  `attachment` varchar(1024) DEFAULT NULL,
  `start_time` date DEFAULT NULL,
  `end_time` date DEFAULT NULL,
  `can_see_dept` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;
CREATE TABLE `t_notific_read_info` (
  `id` int NOT NULL,
  `create_time` date DEFAULT NULL,
  `update_time` date DEFAULT NULL,
  `title_id` varchar(10) DEFAULT NULL,
  `title` varchar(45) DEFAULT NULL,
  `user_id` varchar(45) DEFAULT NULL,
  `type` varchar(1) DEFAULT NULL,
  `read_status` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;
CREATE TABLE `user_info` (
  `id` int NOT NULL,
  `user_id` varchar(45) DEFAULT NULL,
  `user_name` varchar(45) DEFAULT NULL,
  `dept_code` varchar(10) DEFAULT NULL,
  `create_time` date DEFAULT NULL,
  `update_time` varchar(45) DEFAULT NULL,
  `data_status` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
);


--用户表
INSERT INTO `user_info` (`id`, `user_id`, `user_name`, `dept_code`, `create_time`, `update_time`, `data_status`) VALUES ('1', 'lyl', 'lyl', 'c0d001', sysdate(), sysdate(), '1');
INSERT INTO `user_info` (`id`, `user_id`, `user_name`, `dept_code`, `create_time`, `update_time`, `data_status`) VALUES ('2', 'zhangsan', '张三', 'c0d001', sysdate(), sysdate(), '1');
INSERT INTO `user_info` (`id`, `user_id`, `user_name`, `dept_code`, `create_time`, `update_time`, `data_status`) VALUES ('3', 'lisi', '李四', 'ed001', sysdate(), sysdate(), '1');
INSERT INTO `user_info` (`id`, `user_id`, `user_name`, `dept_code`, `create_time`, `update_time`, `data_status`) VALUES ('4', 'wangwu', '王五', 'cdo0011', sysdate(), sysdate(), '1');
INSERT INTO `user_info` (`id`, `user_id`, `user_name`, `dept_code`, `create_time`, `update_time`, `data_status`) VALUES ('5', 'zhaoliu', '赵六', 'c0d011', sysdate(), sysdate(), '1');


--消息表
INSERT INTO `t_notific_info` (`id`, `created_time`, `update_time`, `title`, `type`, `content`, `attachment`, `can_see_dept`) VALUES ('1', sysdate(), sysdate(), '大好河山', '1', '最古哈吉斯地方', 'fasdasas', 'c0d001,ed001,cdo0011,c0d011');
INSERT INTO `t_notific_info` (`id`, `created_time`, `update_time`, `title`, `type`, `content`, `attachment`, `can_see_dept`) VALUES ('2', sysdate(), sysdate(), '测试1', '1', '最古哈吉斯地方', 'fasdasas', 'c0d001,ed001,cdo0011,c0d011');
INSERT INTO `t_notific_info` (`id`, `created_time`, `update_time`, `title`, `type`, `content`, `attachment`, `can_see_dept`) VALUES ('3', sysdate(), sysdate(), '测试2', '1', '最古哈吉斯地方', 'fasdasas', 'ed001,cdo0011,c0d011');
INSERT INTO `t_notific_info` (`id`, `created_time`, `update_time`, `title`, `type`, `content`, `attachment`, `can_see_dept`) VALUES ('4', sysdate(), sysdate(), '测试3', '1', '最古哈吉斯地方', 'fasdasas', 'ed001,cdo0011,c0d011');



--已读信息表
INSERT INTO `t_notific_read_info` (`id`, `create_time`, `update_time`, `title_id`, `title`, `user_id`, `type`, `read_status`) VALUES ('6', sysdate(), sysdate(), '1', '大好河山', 'wangwu', '1', '1');
INSERT INTO `t_notific_read_info` (`id`, `create_time`, `update_time`, `title_id`, `title`, `user_id`, `type`, `read_status`) VALUES ('4', sysdate(), sysdate(), '1', '大好河山', 'wangwu', '1', '1');
INSERT INTO `t_notific_read_info` (`id`, `create_time`, `update_time`, `title_id`, `title`, `user_id`, `type`, `read_status`) VALUES ('5', sysdate(), sysdate(), '2', '测试1', 'wangwu', '1', '1');
INSERT INTO `t_notific_read_info` (`id`, `create_time`, `update_time`, `title_id`, `title`, `user_id`, `type`, `read_status`) VALUES ('3', sysdate(), sysdate(), '1', '测试2', 'lisi', '1', '1');
INSERT INTO `t_notific_read_info` (`id`, `create_time`, `update_time`, `title_id`, `title`, `user_id`, `type`, `read_status`) VALUES ('1', sysdate(), sysdate(), '1', '测试2', 'lisi', '1', '1');
INSERT INTO `t_notific_read_info` (`id`, `create_time`, `update_time`, `title_id`, `title`, `user_id`, `type`, `read_status`) VALUES ('2', sysdate(), sysdate(), '1', '测试2', 'lisi', '1', '1');


SELECT * FROM world.user_info;
SELECT * FROM world.t_notific_info ;
SELECT * FROM world.t_notific_read_info;

用户信息里部门dept_code,一个用户一个部门信息。消息表里消息可见部门can_see_dept，多个部门之间用逗号“，”分割开。已读信息表就是哪些用户读了哪些消息，同一条信息可以有多条，意味着一条信息读取多次。

要求：根据输入用户的user_id，判断消息表中哪些消息，输入的用户可以看,，他的状态是啥（就是有没有读取可见的消息）t_notific_read_info表的数据，谁读取数据了，才会有记录，会出现多条，read_status（1已读，没有数据就是未读）

select t1.*
  ,if(t2.read_status is null ,0,t2.read_status) as read_status
from (
    -- 生成应读表 t1 确保不重复
        select   t3.`id` ,t3.`title` ,t2.`user_id` ,t2.`user_name` ,t3.type,t3.can_see_dept ,t2.`dept_code` 
     from user_info t2   
         inner JOIN   t_notific_info t3
            on INSTR(t3.can_see_dept, t2.`dept_code` ) >0
       and t2.`user_id` = 'lyl'
         group by t3.`id` ,t3.`title` ,t2.`user_id` ,t2.`user_name`, t3.type,t3.can_see_dept ,t2.`dept_code` 
    ) t1 
left join 
(
	-- 生成事实表 t2   确保不重复
 select title_id,user_id,read_status from t_notific_read_info  where `user_id` = 'lyl'group by title_id,user_id,read_status
  ) t2 
 -- 关联条件 是 title 和 user_id 
on t1.id = t2.title_id
 and t1.user_id = t2.user_id;