深入理解mysql之left join 使用详解

ON 子句与 WHERE 子句的不同
    一种更好地理解带有 WHERE ... IS NULL 子句的复杂匹配条件的简单方法
    Matching-Conditions 与 Where-conditions 的不同

关于 “A LEFT JOIN B ON 条件表达式” 的一点提醒

ON 条件(“A LEFT JOIN B ON 条件表达式”中的ON)用来决定如何从 B 表中检索数据行。

如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据

在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后,WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。

让我们看一个 LFET JOIN 示例:

mysql> CREATE TABLE `product` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `amount` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

mysql> CREATE TABLE `product_details` (
  `id` int(10) unsigned NOT NULL,
  `weight` int(10) unsigned default NULL,
  `exist` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> INSERT INTO product (id,amount)
       VALUES (1,100),(2,200),(3,300),(4,400);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO product_details (id,weight,exist)
       VALUES (2,22,0),(4,44,1),(5,55,0),(6,66,1);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM product;
+----+--------+
| id | amount |
+----+--------+
|  1 |    100 |
|  2 |    200 |
|  3 |    300 |
|  4 |    400 |
+----+--------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM product_details;
+----+--------+-------+
| id | weight | exist |
+----+--------+-------+
|  2 |     22 |     0 |
|  4 |     44 |     1 |
|  5 |     55 |     0 |
|  6 |     66 |     1 |
+----+--------+-------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id);
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 |    2 |     22 |     0 |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 |    4 |     44 |     1 |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)

ON 子句和 WHERE 子句有什么不同?

一个问题:下面两个查询的结果集有什么不同么?

1. SELECT * FROM product LEFT JOIN product_details
         ON (product.id = product_details.id)
         AND   product_details.id=2;
2. SELECT * FROM product LEFT JOIN product_details
         ON (product.id = product_details.id)
         WHERE product_details.id=2;

用例子来理解最好不过了:

mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       AND product_details.id=2;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 |    2 |     22 |     0 |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 | NULL |   NULL |  NULL |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       WHERE product_details.id=2;
+----+--------+----+--------+-------+
| id | amount | id | weight | exist |
+----+--------+----+--------+-------+
|  2 |    200 |  2 |     22 |     0 |
+----+--------+----+--------+-------+
1 row in set (0.01 sec)

第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。

第二条查询做了简单的LEFT JOIN,然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。

再来看一些示例:

mysql>
mysql> SELECT * FROM product LEFT JOIN product_details
       ON product.id = product_details.id
       AND product.amount=100;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 | NULL |   NULL |  NULL |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 | NULL |   NULL |  NULL |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)

所有来自product表的数据行都被检索到了,但没有在product_details表中匹配到记录(product.id = product_details.id AND product.amount=100 条件并没有匹配到任何数据)

mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       AND product.amount=200;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 |    2 |     22 |     0 |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 | NULL |   NULL |  NULL |
+----+--------+------+--------+-------+
4 rows in set (0.01 sec)

同样,所有来自product表的数据行都被检索到了,有一条数据匹配到了。

使用 WHERE ... IS NULL 子句的 LEFT JOIN

当你使用 WHERE ... IS NULL 子句时会发生什么呢?

如前所述,WHERE 条件查询发生在 匹配阶段之后,这意味着 WHERE ... IS NULL 子句将从匹配阶段后的数据中过滤掉不满足匹配条件的数据行。

纸面上看起来很清楚,但是当你在 ON 子句中使用多个条件时就会感到困惑了。

我总结了一种简单的方式来理解上述情况:

将 IS NULL 作为否定匹配条件
    使用 !(A and B) == !A OR !B 逻辑判断

看看下面的示例:

mysql> SELECT a.* FROM product a LEFT JOIN product_details b
       ON a.id=b.id AND b.weight!=44 AND b.exist=0
       WHERE b.id IS NULL;
+----+--------+
| id | amount |
+----+--------+
|  1 |    100 |
|  3 |    300 |
|  4 |    400 |
+----+--------+
3 rows in set (0.00 sec)

让我们检查一下 ON 匹配子句:

(a.id=b.id) AND (b.weight!=44) AND (b.exist=0)

我们可以把 IS NULL 子句 看作是否定匹配条件。

这意味着我们将检索到以下行:

!( exist(b.id that equals to a.id) AND b.weight !=44 AND b.exist=0 )
!exist(b.id that equals to a.id) || !(b.weight !=44) || !(b.exist=0)
!exist(b.id that equals to a.id) || b.weight =44 || b.exist=1

就像在C语言中的逻辑 AND 和 逻辑 OR表达式一样,其操作数是从左到右求值的。如果第一个参数做够判断操作结果,那么第二个参数便不会被计算求值(短路效果)

看看别的示例:

mysql> SELECT a.* FROM product a LEFT JOIN product_details b
       ON a.id=b.id AND b.weight!=44 AND b.exist=1
       WHERE b.id IS NULL;
+----+--------+
| id | amount |
+----+--------+
|  1 |    100 |
|  2 |    200 |
|  3 |    300 |
|  4 |    400 |
+----+--------+
4 rows in set (0.00 sec)

Matching-Conditions 与 Where-conditions 之战

如果你吧基本的查询条件放在 ON 子句中,把剩下的否定条件放在 WHERE 子句中,那么你会获得相同的结果。

例如,你可以不这样写:

SELECT a.* FROM product a LEFT JOIN product_details b
ON a.id=b.id AND b.weight!=44 AND b.exist=0
WHERE b.id IS NULL;

你可以这样写:

SELECT a.* FROM product a LEFT JOIN product_details b
ON a.id=b.id
WHERE b.id is null OR b.weight=44 OR b.exist=1;

mysql> SELECT a.* FROM product a LEFT JOIN product_details b
       ON a.id=b.id
       WHERE b.id is null OR b.weight=44 OR b.exist=1;
+----+--------+
| id | amount |
+----+--------+
|  1 |    100 |
|  3 |    300 |
|  4 |    400 |
+----+--------+
3 rows in set (0.00 sec)

你可以不这样写:

SELECT a.* FROM product a LEFT JOIN product_details b
ON a.id=b.id AND b.weight!=44 AND b.exist!=0
WHERE b.id IS NULL;

可以这样写:

SELECT a.* FROM product a LEFT JOIN product_details b
ON a.id=b.id
WHERE b.id is null OR b.weight=44 OR b.exist=0;

mysql> SELECT a.* FROM product a LEFT JOIN product_details b
       ON a.id=b.id
       WHERE b.id is null OR b.weight=44 OR b.exist=0;
+----+--------+
| id | amount |
+----+--------+
|  1 |    100 |
|  2 |    200 |
|  3 |    300 |
|  4 |    400 |
+----+--------+
4 rows in set (0.00 sec)

这些查询真的效果一样?

如果你只需要第一个表中的数据的话,这些查询会返回相同的结果集。有一种情况就是,如果你从 LEFT JOIN的表中检索数据时,查询的结果就不同了。

如前所属,WHERE 子句是在匹配阶段之后用来过滤的。

例如:

mysql> SELECT * FROM product a LEFT JOIN product_details b
       ON a.id=b.id AND b.weight!=44 AND b.exist=1
       WHERE b.id is null;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 | NULL |   NULL |  NULL |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 | NULL |   NULL |  NULL |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM product a LEFT JOIN product_details b
       ON a.id=b.id
       WHERE b.id IS NULL OR b.weight=44 OR b.exist=0;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 |    2 |     22 |     0 |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 |    4 |     44 |     1 |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)

总附注:

如果你使用 LEFT JOIN 来寻找在一些表中不存在的记录,你需要做下面的测试:WHERE 部分的 col_name IS NULL(其中 col_name 列被定义为 NOT NULL),MYSQL 在查询到一条匹配 LEFT JOIN 条件后将停止搜索更多行(在一个特定的组合键下)。

(0)

相关推荐

  • 在MySQL中使用JOIN语句进行连接操作的详细教程

    到目前,我们已经学习了从一个表中获取数据.这是简单的需要,但在大多数现实MySQL的使用,经常需要将数据从多个表中的一个单一的查询. 可以使用多个表中的单一SQL查询.在MySQL中联接(join)行为是指两个或多个表到一个表中可以使用连接在SELECT,UPDATE和DELETE语句中加入MySQL表.我们将看到一个例子LEFT JOIN简单的MySQL连接. 在命令提示符使用联接: 假设我们两个表的教程tcount_tbl和tutorials_tbl的完整列表如下: 例子: 试试下面的例子:

  • MYSQL使用inner join 进行 查询/删除/修改示例

    复制代码 代码如下: --查询 SELECT tp.tp_id, tp.tpmc, tp.leveid, tp.tpdz, tp.jgm, tp.scsj, tp.pbzyid, tp.ksbfsj, tp.jsbfsj, tp.status, tp.tpbz FROM qdgl_tupian tp INNER JOIN qdgl_pqb pq ON tp.tp_id=pq.tpid WHERE pq.bfjgm='27010825' AND ps_bfsj >= '2013-01' AND p

  • MySQL中union和join语句使用区别的辨析教程

    union和join是需要联合多张表时常见的关联词,具体概念我就不说了,想知道上网查就行,因为我也记不准确. 先说差别:union对两张表的操作是合并数据条数,等于是纵向的,要求是两张表字段必须是相同的(Schema of both sides of union should match.).也就是说如果A表中有三条数据,B表中有两条数据,那么A union B就会有五条数据.说明一下union 和union all的差别,对于union如果存在相同的数据记录会被合并,而union all不会合

  • MySQL中视图的使用及多表INNER JOIN的技巧分享

    创建视图   Sql代码 CREATE VIEW view_name AS SELECT t1.xxx, t2.xxx, t3.xxx FROM (table1 t1 INNER JOIN table2 t2 ON t1.fid = t2.fid) INNER JOIN table3 t3 ON t1.mid = t3.mid; 这里使用了3表关联,对于多表关联的 INNER JOIN 写法有一个技巧 1. 先写最简单的2表关联 INNER JOIN 2. 然后使用 () 从 FROM 之后到语

  • MySQL在右表数据不唯一的情况下使用left join的方法

    1.left join 基本用法 mysql left join 语句格式 A LEFT JOIN B ON 条件表达式 left join 是以A表为基础,A表即左表,B表即右表. 左表(A)的记录会全部显示,而右表(B)只会显示符合条件表达式的记录,如果在右表(B)中没有符合条件的记录,则记录不足的地方为NULL. 例如:news 与 news_category表的结构如下,news表的category_id与news_category表的id是对应关系. news 表 news_categ

  • Mysql中Join的使用实例详解

    在前几章节中,我们已经学会了如果在一张表中读取数据,这是相对简单的,但是在真正的应用中经常需要从多个数据表中读取数据. 本章节我们将向大家介绍如何使用MySQL 的 JOIN 在两个或多个表中查询数据. 你可以在SELECT, UPDATE 和 DELETE 语句中使用Mysql 的 join 来联合多表查询. 以下我们将演示MySQL LEFT JOIN 和 JOIN 的使用的不同之处. 在命令提示符中使用JOIN 我们在RUNOOB数据库中有两张表 tcount_tbl 和 runoob_t

  • 在MySQL中使用STRAIGHT_JOIN的教程

    问题 通过「SHOW FULL PROCESSLIST」语句很容易就能查到问题SQL,如下: SELECT post.* FROM post INNER JOIN post_tag ON post.id = post_tag.post_id WHERE post.status = 1 AND post_tag.tag_id = 123 ORDER BY post.created DESC LIMIT 100 说明:因为post和tag是多对多的关系,所以存在一个关联表post_tag. 试着用E

  • 解析mysql left( right ) join使用on与where筛选的差异

    有这样的一个问题mysql查询使用mysql中left(right)join筛选条件在on与where查询出的数据是否有差异.可能只看着两个关键字看不出任何的问题.那我们使用实际的例子来说到底有没有差异. 例如存在两张表结构表结构1 复制代码 代码如下: drop table if EXISTS A;  CREATE TABLE A (    ID int(1) NOT NULL,    PRIMARY KEY  (ID)  ) ENGINE=MyISAM DEFAULT CHARSET=lat

  • 深入理解mysql之left join 使用详解

    ON 子句与 WHERE 子句的不同    一种更好地理解带有 WHERE ... IS NULL 子句的复杂匹配条件的简单方法    Matching-Conditions 与 Where-conditions 的不同 关于 "A LEFT JOIN B ON 条件表达式" 的一点提醒 ON 条件("A LEFT JOIN B ON 条件表达式"中的ON)用来决定如何从 B 表中检索数据行. 如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列

  • mysql中inner join和left join使用详解

    目录 区别 inner join 场景 inner join 场景 区别 返回不同1.inner join只返回两个表中联结字段相等的行2.left join的数量小于等于左表和右表中的记录数量. 数量不同1.inner join返回包括左表中的所有记录和右表中联结字段相等的记录.2.left join的数量以左表中的记录数量相同 记录属性不同1.inner join不足的记录属性会被直接舍弃2.left join不足的记录属性用NULL填充 inner join 场景 设计两张表: chann

  • Mysql调优Explain工具详解及实战演练(推荐)

    Mysql调优Explain工具详解以及实战演练 Explain工具介绍Explain分析示例explain 两个变种explain中的列 索引最佳实战索引使用总结: Mysql安装文档参考 Explain工具介绍 使用EXPLAIN关键字可以模拟优化器执行SQL语句,分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 explain 关键字,MySQL 会在查询上设置一个标记,执行查询会返回执行计划的信息,而不是 执行这条SQL 注意:如果 from 中包含子查询,仍会执行该子

  • MySQL学习之三大范式详解小白篇

    目录 1.范式基础 1.1范式的概念 2.三大范式 2.1三大范式概念 2.2三大范式举例 1.进行第一范式的改造 2.进行第二范式的改造 3.进行第三范式的改造 1.范式基础 1.1范式的概念 设计数据库的时候需要遵从的一些规范,目前关系数据库有六种范式:第一范式(1NF).第二范式(2NF).第三范式(3NF).巴斯-科德范式(BCNF).第四范式(4NF)和第五范式(5NF,又称完美范式). 当然正常情况下我们满足前三个范式就可以设计一个比较规范的数据库 要遵循后边的范式,就必须先遵循前面

  • Mysql数据库group by原理详解

    目录 引言 1. 使用group by的简单例子 2. group by 原理分析 2.1 explain 分析 2.2 group by 的简单执行流程 3. where 和 having的区别 3.1 group by + where 的执行流程 3.2 group by + having 的执行 3.3 同时有where.group by .having的执行顺序 3.4 where + having 区别总结 4. 使用 group by 注意的问题 4.1 group by一定要配合聚

  • MySQL学习之数据更新操作详解

    目录 UPDATE 语句 UPDATE 语句练习① UPDATE 语句练习② UPDATE 语句练习③ UPDATE 语句的表连接 UPDATE 语句练习④ UPDATE 语句 UPDATE 语句用于修改表的记录,语法如下 UPDATE IGNORE 表名 SET 字段1=值1, 字段2=值2, 字段3=值3, ...... WHERE 条件1 ...... ORDER BY ...... LIMIT ......; -- 注意:IGNORE - 是可选条件,使用后,会针对有冲突的修改记录忽略

  • MySQL关联查询优化实现方法详解

    目录 左外连接 内连接INNER JOIN 我们准备如下两个表,并插入数据. #分类 CREATE TABLE IF NOT EXISTS `type` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `card` INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`id`) ); #图书 CREATE TABLE IF NOT EXISTS `book` ( `bookid` INT(10) UNSIGNED NO

  • 关于MySQL查询语句的优化详解

    目录 MySQL 优化 子查询优化 待排序的分页查询的优化 给排序字段添加索引 给排序字段跟 select 字段添加复合索引 给排序字段加索引 + 手动回表 解决办法 排序优化 MySQL 优化 子查询优化 将子查询改变为表连接,尤其是在子查询的结果集较大的情况下: 添加复合索引,其中复合索引的包含的字段应该包括 where 字段与关联字段: 复合索引中的字段顺序要遵守最左匹配原则: MySQL 8 中自动对子查询进行优化: 现有两个表 create table Orders ( id inte

  • mysql数据存储过程参数实例详解

    MySQL 存储过程参数有三种类型:in.out.inout.它们各有什么作用和特点呢? 一.MySQL 存储过程参数(in) MySQL 存储过程 "in" 参数:跟 C 语言的函数参数的值传递类似, MySQL 存储过程内部可能会修改此参数,但对 in 类型参数的修改,对调用者(caller)来说是不可见的(not visible). drop procedure if exists pr_param_in; create procedure pr_param_in ( in id

  • redhat7.1 安装mysql 5.7.10步骤详解(图文详解)

    在redhat下安装MySQL,步骤如下 Mysql目录安装位置:/usr/local/mysql 数据库保存位置:/data/mysql 日志保存位置:/data/log/mysql 下载安装包 http://downloads.mysql.com/archives/community/ 1. 获取mysql安装包,mysql-5.7.10-Linux-glibc2.5-x86_64.tar解压后目录如下. 2. 解压mysql-5.7.10-linux-glibc2.5-x86_64.tar

随机推荐