技术文章
当前位置:首页 > Mysql技术文章 > 深入理解mysql之left join 使用详解

mysql left join使用实例

  • 发布时间:
  • 作者:码农之家原创
  • 点击:184

这篇文章主要知识点是关于left、join、MySQL的LEFT JOIN表连接的进阶学习教程 的内容,如果大家想对相关知识点有系统深入的学习,可以参阅以下电子书

PHP和MySQL Web开发
PHP和MySQL Web开发中文扫描第4版
  • 类型:PHP网站开发大小:51.6 MB格式:PDF出版:机械工业出版社作者:威利,汤姆森
立即下载

更多Mysql相关的学习资源可以参阅 Mysql电子书程序设计电子书 等栏目。

深入理解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 条件后将停止搜索更多行(在一个特定的组合键下)。

MySQL的LEFT JOIN表连接的进阶学习教程

LEFT JOIN的主表

这里所说的主表是指在连接查询里MySQL以哪个表为主进行查询。比如说在LEFT JOIN查询里,一般来说左表就是主表,但这只是经验之谈,很多时候经验主义是靠不住的,为了说明问题,先来个例子,建两个演示用的表categories和posts:

CREATE TABLE IF NOT EXISTS `categories` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(15) NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `posts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`category_id` int(10) unsigned NOT NULL,
`title` varchar(100) NOT NULL,
`content` varchar(200) NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `category_id` (`category_id`)
);

先注意一下每个表的索引情况,以后会用到,记得随便插入一点测试数据,不用太多,但怎么也得两行以上,然后执行以下SQL:

EXPLAIN SELECT *
FROM posts
LEFT JOIN categories ON posts.category_id = categories.id
WHERE categories.id = ‘一个已经存在的ID'
ORDER BY posts.created DESC

table   key     Extra
categories PRIMARY   Using filesort
posts   category_id Using where

在explain的结果中,第一行表示的表就是主表,所以说在此查询里categories是主表,而在我们的经验里,LEFT JOIN查询里,左表(posts表)才应该是主表,这产生一个根本的矛盾,MySQL之所以这样处理,是因为在我们的WHERE部分,查询条件是按照categories表的字段来进行筛选的,而恰恰categories表存在合适的索引,所以在查询时把categories表作为主表更有利于缩小结果集。

那explain结果中的Using filesort又是为什么呢?这是因为主表是categories表,从表是posts表,而我们使用从表的字段去ORDER BY,这通常不是一个好选择,最好改成主表字段,如果鉴于需求所限,无法改成主表的字段,那么可以尝试添加如下索引:

ALTER TABLE `posts` ADD INDEX ( `category_id` , `created` );

再运行SQL时就不会有Using filesort了,这是因为主表categories在通过category_id连接从表posts时,可以进而通过索引直接得到排序后的posts结果。

主观上一旦搞错了主表,可能怎么调整索引都得不到高效的SQL,所以在写SQL时,比如说在写LEFT JOIN查询时,如果希望左表是主表,那么就要保证在WHERE语句里的查询条件尽可能多的使用左表字段,进而,一旦确定了主表,也最好只通过主表字段去ORDER BY。

LEFT JOIN查询效率分析
user表:

id | name
---------
1 | libk
2 | zyfon
3 | daodao

user_action表:

user_id | action
---------------
1 | jump
1 | kick
1 | jump
2 | run
4 | swim

sql:

select id, name, action from user as u
left join user_action a on u.id = a.user_id

result:
id | name | action
--------------------------------
1 | libk | jump ①
1 | libk | kick ②
1 | libk | jump ③
2 | zyfon | run ④
3 | daodao | null ⑤

分析:
注意到user_action中还有一个user_id=4, action=swim的纪录,但是没有在结果中出现,
而user表中的id=3, name=daodao的用户在user_action中没有相应的纪录,但是却出现在了结果集中
因为现在是left join,所有的工作以left为准.
结果1,2,3,4都是既在左表又在右表的纪录,5是只在左表,不在右表的纪录


结论:
我们可以想象left join 是这样工作的
从左表读出一条,选出所有与on匹配的右表纪录(n条)进行连接,形成n条纪录(包括重复的行,如:结果1和结果3),
如果右边没有与on条件匹配的表,那连接的字段都是null.
然后继续读下一条。

引申:
我们可以用右表没有on匹配则显示null的规律, 来找出所有在左表,不在右表的纪录, 注意用来判断的那列必须声明为not null的。
如:

select id, name, action from user as u
left join user_action a on u.id = a.user_id
where a.user_id is NULL

(注意:1.列值为null应该用is null 而不能用=NULL
2.这里a.user_id 列必须声明为 NOT NULL 的)

result:
id | name | action
--------------------------
3 | daodao | NULL

--------------------------------------------------------------------------------

Tips:
1. on a.c1 = b.c1 等同于 using(c1)
2. INNER JOIN 和 , (逗号) 在语义上是等同的
3. 当 MySQL 在从一个表中检索信息时,你可以提示它选择了哪一个索引。
如果 EXPLAIN 显示 MySQL 使用了可能的索引列表中错误的索引,这个特性将是很有用的。
通过指定 USE INDEX (key_list),你可以告诉 MySQL 使用可能的索引中最合适的一个索引在表中查找记录行。
可选的二选一句法 IGNORE INDEX (key_list) 可被用于告诉 MySQL 不使用特定的索引。
4. 一些例子:

mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id);
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
-> LEFT JOIN table3 ON table2.id=table3.id;
mysql> SELECT * FROM table1 USE INDEX (key1,key2)
-> WHERE key1=1 AND key2=2 AND key3=3;
mysql> SELECT * FROM table1 IGNORE INDEX (key3)
-> WHERE key1=1 AND key2=2 AND key3=3;

以上就是本次给大家分享的关于Mysql的全部知识点内容总结,大家还可以在下方相关文章里找到MySQL中的CONCAT函数使用方法、 MySQL性能优化总结、 MySQL 5.7增强版Semisync Repl、 等mysql文章进一步学习,感谢大家的阅读和支持。

上一篇:MySQL修改表(alter table)实例方法

下一篇:MySQL Where 条件语句的实例用法讲解

展开 +

收起 -

mysql实例 相关电子书
学习笔记
网友NO.670424

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_category 表 显示news表记录,并显示news的category名称,查询语句如下 select a.id,a.title,b.name as category_name,a.content,a.addtime,a.lastmodify from news as a left join news_category as b on a.category_id = b.id; 查询结果如下: 因 news_category 表没有id=4的记录,因此news 表中category_id=4的记录的category_name=NULL 使用left join, A表与B表所显示的记录数为 1:1 或 1:0,A表的所有记录都会显示,B表只显示符合条件的记录。 2.left join 右表数据不唯一解决方……

网友NO.865559

MySQL Left JOIN时指定NULL列返回特定值详解

coalesce 函数可以接受多个参数,将会返回这些参数中第一个非NULL的值,若提供的参数全部为NULL,则返回NULL ifnull 函数和coalesce功能一样,只是只可以接受两个参数 if 函数接受三个参数,实现类似于三元判断符(?:)的功能,即第一个参数不为NULL且不为0时,返回第二个参数,否则返回第三个参数 SELECT a.*,coalesce(t.cous,0) as count FROM brand as a left join (select brandid as bid,count(1) as cous from shopbrand group by brandid) t on a.brandid=t.bid ORDER BY count DESC LIMIT 0,20 上述语法是做一个统计,本身会返回NULL,但是为空的话并不是一个好的做法,所以用coalesce来解决 ……

网友NO.165265

mysql多个left join连接查询用法分析

本文实例讲述了mysql多个left join连接查询用法。分享给大家供大家参考,具体如下: mysql查询时需要连接多个表时,比如查询订单的商品表,需要查询商品的其他信息,其他信息不在订单的商品表,需要连接其他库的表,但是连接的条件基本都是商品ID就可以了,先给一个错误语句(查询之间的嵌套,效率很低): SELECT A.order_id, A.wid, A.work_name, A.supply_price, A.sell_price, A.total_num, A.sell_profit, A.sell_percent, A.goods_id, A.goods_name, A.classify, B.gb_nameFROM ( SELECT A.sub_order_id AS order_id, A.photo_id AS wid, A.photo_name AS work_name, A.supply_price, A.sell_price, sum(A.num) AS total_num, ( A.sell_price - A.supply_price ) AS sell_profit, ( A.sell_price - A.supply_price ) / A.sell_price AS sell_percent, A.goods_id, A.goods_name, B.goods_name AS classify FROM order_goods AS A LEFT JOIN ( SELECT A.goods_id, A.parentid, B.goods_name FROM test_qyg_goods.goods AS A LEFT ……

网友NO.358263

mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录

NOT IN、JOIN、IS NULL、NOT EXISTS效率对比 语句一:select count(*) from A where A.a not in (select a from B) 语句二:select count(*) from A left join B on A.a = B.a where B.a is null 语句三:select count(*) from A where not exists (select a from B where A.a = B.a) 知道以上三条语句的实际效果是相同的已经很久了,但是一直没有深究其间的效率对比。一直感觉上语句二是最快的。 今天工作上因为要对一个数千万行数据的库进行数据清除,需要删掉两千多万行数据。大量的用到了以上三条语句所要实现的功能。本来用的是语句一,但是结果是执行速度1个小时32分,日志文件占用21GB。时间上虽然可以接受,但是对硬盘空间的占用确是个问题。因此将所有的语句一都换成语句二。本以为会更快。没想到执行40多分钟后,第一批50000行都没有删掉,反而让SQL SERVER崩溃掉了,结果令人诧异。试了试单独执行这……

网友NO.741878

MySQL的LEFT JOIN表连接的进阶学习教程

LEFT JOIN的主表 这里所说的主表是指在连接查询里MySQL以哪个表为主进行查询。比如说在LEFT JOIN查询里,一般来说左表就是主表,但这只是经验之谈,很多时候经验主义是靠不住的,为了说明问题,先来个例子,建两个演示用的表categories和posts: CREATE TABLE IF NOT EXISTS `categories` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(15) NOT NULL,`created` datetime NOT NULL,PRIMARY KEY (`id`));CREATE TABLE IF NOT EXISTS `posts` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`category_id` int(10) unsigned NOT NULL,`title` varchar(100) NOT NULL,`content` varchar(200) NOT NULL,`created` datetime NOT NULL,PRIMARY KEY (`id`),KEY `category_id` (`category_id`)); 先注意一下每个表的索引情况,以后会用到,记得随便插入一点测试数据,不用太多,但怎么也得两行以上,然后执行以下SQL: EXPLAIN SELECT *FROM postsLEFT JOIN categories ON posts.category_id = categorie……

<
1
>

Copyright 2018-2020 xz577.com 码农之家

电子书资源由网友、会员提供上传,本站记录提供者的基本信息及资源来路

鸣谢: “ 码小辫 ” 公众号提供回调API服务、“ 脚本CDN ”提供网站加速(本站寻求更多赞助支持)

版权投诉 / 书籍推广 / 赞助:520161757@qq.com

上传资源(网友、会员均可提供)

查看最新会员资料及资源信息