11

MySQL left join操作中on和where放置条件的区别介绍

 1 year ago
source link: https://blog.51cto.com/lenglingx/6859193
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

MySQL left join操作中on和where放置条件的区别介绍

精选 原创

lenglingx 2023-07-26 19:21:11 博主文章分类：mysql/mariadb ©著作权

文章标签 ci 查询语句 数据 文章分类 代码人生 阅读数188

在用left join中，on和where条件的区别如下：

    可以认为left join 返回的是一个生成的临时表，on就是生成这个临时表的条件；where是过滤这个临时结果表的，他和left join on已经没有什么关系了。

（1）on条件是在生成中间表时使用的条件，它不管on中的条件是否为真，都会返回左表中的记录。

（2）where条件是在中间表生成好之后，再对这张表进行果过滤，这时已经和left join没有关系，不符合where条件的就会被过滤掉。

一 、 准备基础数据

数据.sql


DROP TABLE IF EXISTS `table1`;

CREATE TABLE `table1` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `No` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'No',
  `NoDesc` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'No描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Data for the table `table1` */

insert  into `table1`(`id`,`No`,`NoDesc`) values (1,'n1','N1描述'),(2,'n2','N2描述'),(3,'n3','N3描述'),(4,'n4','N4描述');

/*Table structure for table `table2` */

DROP TABLE IF EXISTS `table2`;

CREATE TABLE `table2` (
  `No` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'No',
  `Name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '名字',
  `Mark` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Data for the table `table2` */

insert  into `table2`(`No`,`Name`,`Mark`) values ('n1','aaa','备注a'),('n2','bbb','备注b'),('n3','ccc','备注c'),('n4','ddd','备注d'),('n5','eee','备注e'),('n6','fff','备注f');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

先来table1表；（4条记录）

SELECT * FROM table1 ;
MySQL left join操作中on和where放置条件的区别介绍_ci

再看table2表; （6条记录）

SELECT * FROM table2  ;
MySQL left join操作中on和where放置条件的区别介绍_查询语句_02

（一） 左右表取某个字段进行相等关联时（最常见，最常使用）

table1左连接table2（table1 left join table2）

查询语句：

SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark 
FROM table1 a 
LEFT JOIN table2 b ON (a.No = b.No)

查询结果：（返回4条记录）

MySQL left join操作中on和where放置条件的区别介绍_ci_03

table2左连接table1（table2 left join table1）

查询语句：（返回6条记录）

SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark 
FROM table2 b 
LEFT JOIN table1 a ON (a.No = b.No)

查询结果：

MySQL left join操作中on和where放置条件的区别介绍_数据_04

table1左连接table2，并使用where过滤临时生成结果，返回需要数据（满足where条件的）

查询语句：

SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark FROM table1 a LEFT JOIN table2 b ON (a.No = b.No);
SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark FROM table1 a LEFT JOIN table2 b ON (a.No = b.No) WHERE b.name='aaa';

查询结果：

MySQL left join操作中on和where放置条件的区别介绍_ci_05
MySQL left join操作中on和where放置条件的区别介绍_数据_06

（二） 左连接查询ON条件时只有右表某个字段条件

被连接的表on条件返回的是一个值的情况

上面的2个案例的连接都是on a.x = b.x为条件；如果on的条件只是被连接表中的一个条件时；如

SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark 
FROM table1 a 
LEFT JOIN table2 b ON b.name = 'aaa'
MySQL left join操作中on和where放置条件的区别介绍_数据_07

结论：左表的每个数据与on右表满足条件的值进行连接，左表4个结果，右表1个结果，所以返回4个结果。

被连接的表on条件返回的是多个值的情况

SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark 
FROM table1 a 
LEFT JOIN table2 b ON b.name != 'aaa'
MySQL left join操作中on和where放置条件的区别介绍_ci_08

结论：左表的每个数据与on右表满足条件的值进行连接，左表4个结果，右表5个结果，所以返回4*5=20个结果。

（三） 左连接查询ON条件时只有左表某个字段条件

连接的表on条件返回的是一个值的情况

查询语句：

SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark 
FROM table1 a 
LEFT JOIN table2 b ON a.No = 'n2'

查询结果：

MySQL left join操作中on和where放置条件的区别介绍_查询语句_09

结论：连接表根据on条件，返回1条值，它与被连接表的所有记录（6条）进行直接关联，得到6条记录；然后把连接表剩余记录3条记录与null进行直接关联；最终得到9条记录。

连接的表on条件返回的是多个值的情况

查询语句：

SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark 
FROM table1 a 
LEFT JOIN table2 b ON a.No != 'n2'

查询结果：

MySQL left join操作中on和where放置条件的区别介绍_查询语句_10

结论：连接表根据on条件，返回3条值，它与被连接表的所有记录（6条）进行直接关联，得到18条记录；然后把连接表剩余记录1条记录与null进行直接关联；最终得到19条记录。

（四） 左右表取某个字段进行相等关联同时还加上其他条件

只要2张表有相等关联后，on后面的and连接条件 其实都可以理解为对被连接表条件现在，虽然条件写的a.No='n2'，但是对应为b.No='n2'，或者说b.Name='bbb'。

可以简单理解为

SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark FROM table1 a LEFT JOIN table2 b ON (a.No = b.No AND a.No='n2');
SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark FROM table1 a LEFT JOIN table2 b ON (a.No = b.No AND b.No='n2');
SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark FROM table1 a LEFT JOIN table2 b ON (a.No = b.No AND b.Name='bbb');
MySQL left join操作中on和where放置条件的区别介绍_ci_11

下面是各个案例的查询结果：

SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark FROM table1 a LEFT JOIN table2 b ON (a.No = b.No AND b.name='aaa');
SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark FROM table1 a LEFT JOIN table2 b ON (a.No = b.No AND b.name != 'aaa');

SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark FROM table1 a LEFT JOIN table2 b ON (a.No = b.No AND a.No='n2');
SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark FROM table1 a LEFT JOIN table2 b ON (a.No = b.No AND a.No != 'n2');

SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark FROM table1 a LEFT JOIN table2 b ON (a.No = b.No AND b.name!='aaa' AND a.No!='n2');
SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark FROM table1 a LEFT JOIN table2 b ON (a.No = b.No AND b.name='aaa');
MySQL left join操作中on和where放置条件的区别介绍_数据_12
SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark FROM table1 a LEFT JOIN table2 b ON (a.No = b.No AND b.name != 'aaa');
MySQL left join操作中on和where放置条件的区别介绍_ci_13
SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark FROM table1 a LEFT JOIN table2 b ON (a.No = b.No AND a.No='n2');
MySQL left join操作中on和where放置条件的区别介绍_查询语句_14
SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark FROM table1 a LEFT JOIN table2 b ON (a.No = b.No AND a.No != 'n2');
MySQL left join操作中on和where放置条件的区别介绍_查询语句_15
SELECT a.id, a.No, a.NoDesc, b.No, b.Name, b.Mark FROM table1 a LEFT JOIN table2 b ON (a.No = b.No AND b.name!='aaa' AND a.No!='n2');
MySQL left join操作中on和where放置条件的区别介绍_ci_16

RIGHT JOIN 和 LEFT JOIN 一样，只是 连接和背连接的表的位置变了，其他一样。

  • 赞
  • 收藏
  • 评论
  • 分享
  • 举报

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK