mysql left join、right join、inner join用法分析

四种联接

left join(左联接)

返回包括左表中的所有记录和右表中联结字段相等的记录

right join(右联接)

返回包括右表中的所有记录和左表中联结字段相等的记录

inner join(等值联接)

只返回两个表中联结字段相等的行

cross join(交叉联接)

得到的结果是两个表的乘积,即笛卡尔积

创建表
CREATE TABLE `product` (`id` int(10) unsigned not null auto_increment,`amount` int(10) unsigned default null,PRIMARY KEY  (`id`)) ENGINE=innodb;
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=innodb;

插入数据

INSERT INTO product (id,amount) VALUES (1,100),(2,200),(3,300),(4,400);
INSERT INTO product_details (id,weight,exist) VALUES (2,22,0),(4,44,1),(5,55,0),(6,66,1);
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)

inner join(等值联接)

mysql> select * from product a inner join product_details b on a.id=b.id;
+----+--------+----+--------+-------+ 
| id | amount | id | weight | exist | 
+----+--------+----+--------+-------+   
|  2 |    200 |    2 |     22 |     0 |  
|  4 |    400 |    4 |     44 |     1 |   
 +----+--------+----+--------+-------+ 
 2 rows in set (0.00 sec)

left join(左联接)

mysql>   select * from product a left join product_details b on a.id=b.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 |
+----+--------+------+--------+-------+
mysql> select * from product a left join product_details b on a.id=b.id and b.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 |
+----+--------+------+--------+-------+     
mysql> select * from product a left join product_details b on a.id=b.id where b.id=2; 
+----+--------+----+--------+-------+
| id | amount | id | weight | exist |
+----+--------+----+--------+-------+
|  2 |    200 |    2 |     22 |     0 |
+----+--------+----+--------+-------+  
mysql> select * from product a left join product_details b on a.id=b.id where a.id=3; 
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist|
+----+--------+------+--------+-------+
|  3 |    300 | NULL |   NULL |    NULL |
+----+--------+------+--------+-------+     
mysql> select * from product a left join product_details b on a.id=b.id   and a.id=3;  
+----+--------+------+--------+-------+
| 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 |
+----+--------+------+--------+-------+     
mysql> SELECT * 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 | id   | weight | exist   |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |    NULL |
|  3 |    300 | NULL |   NULL |    NULL |
|  4 |    400 | NULL |   NULL |    NULL |
+----+--------+------+--------+-------+     
mysql> SELECT * 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 not NULL;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist   |
+----+--------+------+--------+-------+
|  2 |    200 |      2 |     22 |     0 |
+----+--------+------+--------+-------+

right join跟left join相反,不多做解释,MySQL本身不支持所说的full join(全连接),但可以通过union来实现。

mysql>   select * from product a left join product_details b on a.id=b.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 |
+----+--------+------+--------+-------+
mysql> select * from product a left join product_details b on a.id=b.id and b.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 |
+----+--------+------+--------+-------+ 
mysql> select * from product a left join product_details b on a.id=b.id where b.id=2; 
+----+--------+----+--------+-------+
| id | amount | id | weight | exist |
+----+--------+----+--------+-------+
|  2 |    200 |    2 |     22 |     0 |
+----+--------+----+--------+-------+  
mysql> select * from product a left join product_details b on a.id=b.id where a.id=3; 
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist|
+----+--------+------+--------+-------+
|  3 |    300 | NULL |   NULL |    NULL |
+----+--------+------+--------+-------+     
mysql> select * from product a left join product_details b on a.id=b.id   and a.id=3;  
+----+--------+------+--------+-------+
| 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 |
+----+--------+------+--------+-------+     
mysql> SELECT * 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 | id   | weight | exist   |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |    NULL |
|  3 |    300 | NULL |   NULL |    NULL |
|  4 |    400 | NULL |   NULL |    NULL |
+----+--------+------+--------+-------+     
mysql> SELECT * 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 not NULL;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist   |
+----+--------+------+--------+-------+
|  2 |    200 |      2 |     22 |     0 |
+----+--------+------+--------+-------+

Cross join(交叉联接)

cross join:交叉联接,得到的结果是两个表的乘积,即笛卡尔积。

笛卡尔(Descartes)乘积又叫直积。

假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。

类似的例子有,如果A表示某学校学生的集合,B表示该学校所有课程的集合,则A与B的笛卡尔积表示所有可能的选课情况。

mysql>  select * from product a cross join   product_details b;
+----+--------+----+--------+-------+
| id | amount | id | weight | exist |
+----+--------+----+--------+-------+
|  1 |    100 |    2 |     22 |     0 |
|  2 |    200 |    2 |     22 |     0 |
|  3 |    300 |    2 |     22 |     0 |
|  4 |    400 |    2 |     22 |     0 |
|  1 |    100 |    4 |     44 |     1 |
|  2 |    200 |    4 |     44 |     1 |
|  3 |    300 |    4 |     44 |     1 |
|  4 |    400 |    4 |     44 |     1 |
|  1 |    100 |    5 |     55 |     0 |
|  2 |    200 |    5 |     55 |     0 |
|  3 |    300 |    5 |     55 |     0 |
|  4 |    400 |    5 |     55 |     0 |
|  1 |    100 |    6 |     66 |     1 |
|  2 |    200 |    6 |     66 |     1 |
|  3 |    300 |    6 |     66 |     1 |
|  4 |    400 |    6 |     66 |     1 |
+----+--------+----+--------+-------+

on与 where的执行顺序

ON 条件(“A LEFT JOIN B ON 条件表达式”中的ON)用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后,WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。

所以我们要注意:在使用Left (right) join的时候,一定要在先给出尽可能多的匹配满足条件,减少Where的执行。

A Left join B On a.id=b.idAnd b.id=2;从B表中检索符合的所有数据行,如果没有匹配的全部为null A Left join B On a.id=b.idWhere b.id=2;先做left join 再过滤, WHERE 条件查询发生在匹配阶段之后

原文发布于微信公众号 - MYSQL轻松学(learnmysql)

原文发表时间:2016-09-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java后端生活

MySQL(九)DQL之子查询

1318
来自专栏杨建荣的学习笔记

相同update语句在MySQL,Oracle的不同表现(r12笔记第30天)

今天有个朋友问我一个SQL问题,大体是一个update语句,看起来逻辑没有问题,但是执行的时候却总是报错。 语句和报错信息为: UPDATE paymen...

3487
来自专栏跟着阿笨一起玩NET

winfrom如何做一个语法着色控件

本文转载:http://www.cnblogs.com/hexin0614/archive/2012/01/17/2324224.html

351
来自专栏个人随笔

MySQL高级查询

 高级查询     关键字书写顺序  关键字执行顺序 select:投影结果       1    5 from:定位到表             2   ...

4109
来自专栏calmound

SQL语句知识点

PERSON表 NUMBER NAME SEX AGE 1 THERON male 19 2 JACK male 20 3 LUCY fema...

3156
来自专栏跟着阿笨一起玩NET

(3)合并列值与分拆列值

在SQL中分拆列值和合并列值老生常谈了,从网上搜刮了一下并记录下来,以便不时之需 :)

321
来自专栏iMySQL的专栏

分区表场景下的 SQL 优化

有个表做了分区,每天一个分区。该表上有个查询,经常只查询表中某一天数据,但每次都几乎要扫描整个分区的所有数据,有什么办法进行优化吗?

750
来自专栏Netkiller

新闻数据库分表案例

文章节选自《Netkiller Architect 手札》 6.3. 新闻数据库分表案例 这里我通过一个新闻网站为例,解决分表的问题 避免开发中经常拼接表,我采...

3426
来自专栏测试开发架构之路

分分钟搞懂union与union all

SQL UNION 操作符 UNION 操作符用于合并两个或多个 SELECT 语句的结果集。 请注意,UNION 内部的 SELECT 语句必须拥有相同数量的...

3259
来自专栏逸鹏说道

利用insert,update和delete注入获取数据

0x00 简介 利用SQL注入获取数据库数据,利用的方法可以大致分为联合查询、报错、布尔盲注以及延时注入,通常这些方法都是基于select查询语句中的SQL注射...

3168

扫描关注云+社区