深入浅出：MySQL的左连接、右连接、等值连接

最新推荐文章于 2023-02-11 15:53:48 发布
最新推荐文章于 2023-02-11 15:53:48 发布
阅读量6.8w 收藏 20
点赞数 5
分类专栏： Mysql Management 文章标签： mysql table null join character sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wyzxg/article/details/7276979
版权
author:skate
time:2012/02/18

 

深入浅出：MySQL的左连接、右连接、等值连接

 

三种连接的语法

为便于更多的技友快速读懂、理解，我们只讨论2张表对象进行连接操作的情况，大于2张表对象进行的连接操作原理也是一样的。

 

1.左连接（LEFT JOIN ）

SELECT M.columnname……,N.* columnname…..

FROM left_table M LEFT JOIN right_table N ON M.columnname_join=N.columnname_join AND N.columnname=XXX

WHERE M.columnname=XXX…..

 

2.右连接（RIGHT JOIN）
SELECT M.columnname……,N.* columnname…..

FROM left_table M RIGHT JOIN right_table N ON M. columnname_join=N. columnname_join AND M. columnname=XXX

WHERE N.columnname=XXX…..

 

3.等值连接

SELECT M.columnname……,N.* columnname…..

FROM left_table M [INNER] JOIN right_table N ON M. columnname_join=N. columnname_join

WHERE M.columnname=XXX….. AND N.columnname=XXX….

或者

SELECT M.columnname……,N.* columnname…..

FROM left_table M  , right_table N

WHERE M. columnname_join=N. columnname_join AND
M.columnname=XXX….. AND N.columnname=XXX….

 

说明：

 

A.左连接（LEFT JOIN ）

ON字句连接条件，用于把2表中等值的记录连接在一起，但是不影响记录集的数量。若是表left_table中的某记录，无法在表right_table找到对应的记录，则此记录依然显示在记录集钟，只是表 right_table需要在查询显示的列的值用NULL替代；

 

ON字句连接条件中表right_table.columnname=XXX用于控制right_table表是否有符合要求的列值还是用NULL替换的方式显示在查询列中，不影响记录集的数量；

 

WHERE字句控制记录是否符合查询要求，不符合则过滤掉；

 

简单点说，就是 “left_table.columnname_join=right_table.columnname_join”用于统计两个表的等值连接的记录集，on子句
连接条件就是限制right_table的记录集大小，where子句的条件是限制最终结果集的大小。最终结果集的大小是显示“left_table“表的所有记录，和“left_table“匹配的”right_table“的记录显示”right_table“列值，不匹配的话，显示对应的”right_table“显示为null（最终结果集的大小是left_table刨除where条件的记录数）


B.右连接（RIGHT JOIN）

ON子句连接条件，用于把2表中等值的记录连接在一起，若是表right_table中的某记录，无法在表left_table找到对应的记录，则表 left_table需要在查询显示的列的值用NULL替代；

 

ON子句连接条件中表left_table.columnname=XXX用于控制left_table表是否有符合要求的列值，还是用NULL替换的方式显示在查询列表中；

 

WHERE字句控制记录是否符合查询要求，不符合则过滤掉；

 

简单点说，就是 “left_table.columnname_join=right_table.columnname_join”用于统计两个表的等值连接的记录集，on子句
连接条件就是限制left_table的记录集大小，where子句的条件是限制最终结果集的大小。最终结果集的大小是显示“right_table”表的所有记录，和“right_table“匹配的”left_table“的记录显示”left_table“列值，不匹配的话，显示对应的”left_table“显示为null（最终结果集的大小是left_table刨除where条件的记录数）


C. 等值连接

ON子句连接条件，不再与左连接或右连接的功效一样，除了作为2表记录匹配的条件外，还会起到过滤记录的作用，若left_table中记录无法在right_table中找到对应的记录，则会被过滤掉；

 

 WHERE字句，不管是涉及表left_table、表right_table上的限制条件，还是涉及2表连接的条件，都会对记录集起到过滤作用，把不符合要求的记录刷选掉；


 
测试数据：

 

创建表
mysql> CREATE TABLE left_table(ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> Username VARCHAR(40) NOT NULL,
    -> Birthday DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    -> CityID     SMALLINT NOT NULL DEFAULT 0,
    -> CreatDate  TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    -> AlterDate  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> PRIMARY KEY(ID),
    -> KEY idx_username(Username)
    -> )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
Query OK, 0 rows affected (0.11 sec)

 

mysql>
mysql> CREATE TABLE right_table(UID INT UNSIGNED NOT NULL ,
    -> CollectNum MEDIUMINT NOT NULL DEFAULT 0,
    -> BuyNum     MEDIUMINT NOT NULL DEFAULT 0,
    -> SearchNum  MEDIUMINT NOT NULL DEFAULT 0,
    -> CreatDate  TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    -> AlterDate  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> PRIMARY KEY(UID)
    -> )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
Query OK, 0 rows affected (0.00 sec)

 

插入测试数据

执行如下sql十次
 INSERT INTO left_table(Username,Birthday,CityID,CreatDate,AlterDate)
 VALUES(CONCAT(SUBSTRING(RAND(),3,8),'@qq.com'),DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),3,2) YEAR),SUBSTRING(RAND(),3,2),DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),3,3) DAY),DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),3,2) DAY));


执行一次如下sql
 INSERT INTO right_table
 SELECT ID,SUBSTRING(RAND(),3,4) AS CollectNum,SUBSTRING(RAND(),3,2) AS BuyNum,SUBSTRING(RAND(),3,3) AS       SearchNum,CreatDate,AlterDate FROM left_table WHERE ID%5=1;

查看基础数据：

mysql> select * from right_table;
+-----+------------+--------+-----------+---------------------+---------------------+
| UID | CollectNum | BuyNum | SearchNum | CreatDate           | AlterDate           |
+-----+------------+--------+-----------+---------------------+---------------------+
|   1 |       2545 |     78 |       171 | 2010-01-13 17:42:37 | 2011-12-16 17:42:37 |
|   6 |       4951 |     96 |       325 | 2009-11-28 17:42:55 | 2011-12-10 17:42:55 |
+-----+------------+--------+-----------+---------------------+---------------------+
2 rows in set (0.00 sec)

 

mysql> select * from left_table;
+----+-----------------+---------------------+--------+---------------------+---------------------+
| ID | Username        | Birthday            | CityID | CreatDate           | AlterDate           |
+----+-----------------+---------------------+--------+---------------------+---------------------+
|  1 | 27311524@qq.com | 1995-02-20 17:42:37 |      5 | 2010-01-13 17:42:37 | 2011-12-16 17:42:37 |
|  2 | 01644610@qq.com | 2004-02-20 17:42:49 |     39 | 2010-03-02 17:42:49 | 2012-01-10 17:42:49 |
|  3 | 89103050@qq.com | 1990-02-20 17:42:52 |     44 | 2010-08-28 17:42:52 | 2012-01-14 17:42:52 |
|  4 | 27363743@qq.com | 1989-02-20 17:42:53 |     32 | 2009-07-09 17:42:53 | 2011-12-03 17:42:53 |
|  5 | 09483603@qq.com | 2003-02-20 17:42:54 |     19 | 2010-03-25 17:42:54 | 2011-11-23 17:42:54 |
|  6 | 38929060@qq.com | 1987-02-20 17:42:55 |     11 | 2009-11-28 17:42:55 | 2011-12-10 17:42:55 |
|  7 | 15852433@qq.com | 1949-02-20 17:42:56 |     68 | 2010-09-17 17:42:56 | 2011-12-27 17:42:56 |
|  8 | 22581017@qq.com | 1967-02-20 17:42:57 |     59 | 2010-06-18 17:42:57 | 2012-01-24 17:42:57 |
|  9 | 54863339@qq.com | 1921-02-20 17:42:59 |     91 | 2009-11-23 17:42:59 | 2012-01-15 17:42:59 |
| 10 | 36242195@qq.com | 1940-02-20 17:43:00 |     51 | 2011-01-09 17:43:00 | 2012-01-02 17:43:00 |
+----+-----------------+---------------------+--------+---------------------+---------------------+
10 rows in set (0.00 sec)

 

mysql> select m.id,m.username from left_table m where m.id<=6;
+----+-----------------+
| id | username        |
+----+-----------------+
|  1 | 27311524@qq.com |
|  2 | 01644610@qq.com |
|  3 | 89103050@qq.com |
|  4 | 27363743@qq.com |
|  5 | 09483603@qq.com |
|  6 | 38929060@qq.com |
+----+-----------------+
6 rows in set (0.02 sec)

 

左连接(left join)

 

编号：sql_1
mysql> select m.id,m.username,n.CollectNum,n.BuyNum from left_table m left join right_table n on m.id=n.uid where m.id <=6;
+----+-----------------+------------+--------+
| id | username        | CollectNum | BuyNum |
+----+-----------------+------------+--------+
|  1 | 27311524@qq.com |       2545 |     78 |
|  2 | 01644610@qq.com |       NULL |   NULL |
|  3 | 89103050@qq.com |       NULL |   NULL |
|  4 | 27363743@qq.com |       NULL |   NULL |
|  5 | 09483603@qq.com |       NULL |   NULL |
|  6 | 38929060@qq.com |       4951 |     96 |
+----+-----------------+------------+--------+
6 rows in set (0.02 sec)

 

编号：sql_2
mysql> select m.id,m.username,n.CollectNum,n.BuyNum from left_table m left join right_table n on m.id=n.uid and  n.SearchNum>300 where m.id <=6;
+----+-----------------+------------+--------+
| id | username        | CollectNum | BuyNum |
+----+-----------------+------------+--------+
|  1 | 27311524@qq.com |       NULL |   NULL |
|  2 | 01644610@qq.com |       NULL |   NULL |
|  3 | 89103050@qq.com |       NULL |   NULL |
|  4 | 27363743@qq.com |       NULL |   NULL |
|  5 | 09483603@qq.com |       NULL |   NULL |
|  6 | 38929060@qq.com |       4951 |     96 |
+----+-----------------+------------+--------+
6 rows in set (0.00 sec)

 

sql_1和sql_2对比
1.两个结果集的记录数是一样的，left_table显示的列值是一样的
2.两个结果集中right_table相应列值是不一样的

 

为什么会不一样呢？
两个记录集的记录数是一样的，那是因为两个sql的where条件是一样，即最终的结果集的数量就会一样，right_table相应的列值不一样那是因为在on的条件中sql_2过滤掉了right_table的符合条件的记录。


右连接也就类似了，下面说下等值连接。

 

带join的等值连接
mysql> select m.id,m.username,n.CollectNum,n.BuyNum
    -> from left_table m inner join right_table n on m.id=n.uid
    -> where m.id<=6;
+----+-----------------+------------+--------+
| id | username        | CollectNum | BuyNum |
+----+-----------------+------------+--------+
|  1 | 27311524@qq.com |       2545 |     78 |
|  6 | 38929060@qq.com |       4951 |     96 |
+----+-----------------+------------+--------+
2 rows in set (0.00 sec)


mysql> select m.id,m.username,n.CollectNum,n.BuyNum from left_table m inner join right_table n on m.id=n.uid and n.SearchNum >300 where m.id<=6;
+----+-----------------+------------+--------+
| id | username        | CollectNum | BuyNum |
+----+-----------------+------------+--------+
|  6 | 38929060@qq.com |       4951 |     96 |
+----+-----------------+------------+--------+
1 row in set (0.01 sec)

 

从上面的可以看到，这里的on有了过滤最终结果集的大小的作用了

 

不带join的等值连接（这种方式我用着比较舒服，因为一直用oracle的）
mysql> select m.id,m.username,n.CollectNum,n.BuyNum
    -> from left_table m,right_table n
    -> where m.id=n.uid and m.id<=6;
+----+-----------------+------------+--------+
| id | username        | CollectNum | BuyNum |
+----+-----------------+------------+--------+
|  1 | 27311524@qq.com |       2545 |     78 |
|  6 | 38929060@qq.com |       4951 |     96 |
+----+-----------------+------------+--------+
2 rows in set (0.00 sec)

mysql>

 

常见错误用法的左连接

 

1.错把限制表right_table的条件，从ON子句中放到WHERE字句中，这样就会影响最终记录集大小

mysql> select m.id,m.username,n.CollectNum,n.BuyNum from left_table m left join right_table n on m.id=n.uid and  n.SearchNum>300 where m.id <=6;
+----+-----------------+------------+--------+
| id | username        | CollectNum | BuyNum |
+----+-----------------+------------+--------+
|  1 | 27311524@qq.com |       NULL |   NULL |
|  2 | 01644610@qq.com |       NULL |   NULL |
|  3 | 89103050@qq.com |       NULL |   NULL |
|  4 | 27363743@qq.com |       NULL |   NULL |
|  5 | 09483603@qq.com |       NULL |   NULL |
|  6 | 38929060@qq.com |       4951 |     96 |
+----+-----------------+------------+--------+
6 rows in set (0.00 sec)

 

select m.id,m.username,n.CollectNum,n.BuyNum from left_table m ,right_table n where m.id=n.uid and  m.id <=6 and n.SearchNum > 300;
+----+-----------------+------------+--------+
| id | username        | CollectNum | BuyNum |
+----+-----------------+------------+--------+
|  6 | 38929060@qq.com |       4951 |     96 |
+----+-----------------+------------+--------+
1 row in set (0.00 sec)

 


2.错把限制表left_table或称影响最终记录集的条件，从WHERE子句中放到ON字句中，这样就相当于增大的最终结果集记录数

select m.id,m.username,n.CollectNum,n.BuyNum from left_table m ,right_table n where m.id=n.uid and  m.id <=6 and n.SearchNum > 300;
+----+-----------------+------------+--------+
| id | username        | CollectNum | BuyNum |
+----+-----------------+------------+--------+
|  6 | 38929060@qq.com |       4951 |     96 |
+----+-----------------+------------+--------+
1 row in set (0.00 sec)


mysql> select m.id,m.username,n.CollectNum,n.BuyNum from left_table m left join right_table n on m.id=n.uid and  n.SearchNum>300 where m.id <=6;
+----+-----------------+------------+--------+
| id | username        | CollectNum | BuyNum |
+----+-----------------+------------+--------+
|  1 | 27311524@qq.com |       NULL |   NULL |
|  2 | 01644610@qq.com |       NULL |   NULL |
|  3 | 89103050@qq.com |       NULL |   NULL |
|  4 | 27363743@qq.com |       NULL |   NULL |
|  5 | 09483603@qq.com |       NULL |   NULL |
|  6 | 38929060@qq.com |       4951 |     96 |
+----+-----------------+------------+--------+
6 rows in set (0.00 sec)

 

 

 

 

 

----------end----------
 

skate
关注
  • 5
    点赞
  • 踩
  • 20
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
剖析：MySQL的左连接、右连接、等值连接异同
01-31
为便于更多的技友快速读懂、理解，我们只讨论2张表对象进行连接操作的情况，大于2张表对象进行的连接操作原理也是一样的。SELECTM.columnname……,N.*columnname…..FROMleft_tableMLEFTJOINright_table NONM.columnname_join=N.columnname_joinANDN.columnname=XXXWHEREM.columnname=XXX…..SELECTM.columnname……,N.*columnname…..
剖析MySQL左连接/右连接/等值连接异同
liupeng900605的专栏
12-13 1093
从业以来，发现很多技术人员和同事向笔者或是在网络上咨询过LEFT JOIN 如何写及其特性，而等值连接一般都没问题，几年前曾经写过一份Microsoft SQL Server版本的，此次特意再写一份MySQL版本的，希望阅读过的技术人员，可以起到解惑功效。 　　1、三种连接的语法 　　为便于更多的技友快速读懂、理解，我们只讨论2张表对象进行连接操作的情况，大于2张表对象进行的连接操作原
MySQL的左联，右联，内联，等值连接的区别
yangyang0826的博客
11-07 750
数据准备：单纯为了对比左联，右联，内联的区别所做的数据，不考虑业务逻辑 CREATE TABLE student( sid INT PRIMARY KEY, sname VARCHAR(10) NOT NULL, sage INT(10), scity VARCHAR(10) )DEFAULT CHARSET='utf8'; CREATE TABLE scourse( sid INT PRIMARY KEY, cid INT(10), cname VARCHAR(10) )DEFAULT CHARSE
等值连接、内连接、左连接(左外连接)、右连接(右外连接)、全连接、笛卡尔积
小目标一个亿
01-10 1万+
UserInfo表： WorkInfo表： 1、等值连接：从左表中取出每一条记录，去右表中与所有的记录进行匹配：匹配必须是某个条件在左表中与右表中相同最终才会保留结果，否则不保留。 2、内连接 以上可以看出，内连接有两种写法，一种是inner join，另一种是join，这两种写法都是一样的，可以理解为join是inner join的缩写。还可以看出，等值连接和内连接的...
【task12】非等值连结
weixin_45746689的博客
02-10 503
目录非等值自左连结 除了使用相等判断的等值连结, 也可以使用比较运算符来进行连接. 实际上, 包括比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件. 非等值自左连结 使用非等值自左连结实现排名 练习题: 希望对 Product 表中的商品按照售价赋予排名. 一个从集合论出发,使用自左连结的思路是, 对每一种商品,找出售价不低于它的所有商品, 然后对售价不低于它的商品使用 COU
mysql left( right ) join使用on 与where 筛选的差异
xiangluping的专栏
01-12 767
   有这样的一个问题mysql查询使用mysql中left(right)join筛选条件在on与where查询出的数据是否有差异。    可能只看着两个关键字看不出任何的问题。那我们使用实际的例子来说到底有没有差异。      例如存在两张表结构    表结构1   drop table if EXISTS A; CREATE TABLE A ( ID int(1) NOT ...
等值连接与自然连接的区别
weixin_41263532的博客
12-04 7426
一个选择题： 等值连接和自然连接相比较，正确的是 A等值连接和自然连接的结果完全相同 B等值连接的属性个数大于自然连接的属性个数 C等值连接的属性个数大于或等于自然连接的属性个数 D等值连接和自然连接的连接条件相同 等值连接：连接的一种。等值连接可以比较不同的属性，选择R与S的笛卡尔积中，A属性的值==B属性的值，也不删除重复的元组。 自然连接，互相比较的属性必须是相同的，并且去除重复的属性组。 所以选择B选项，这个题目我第一次做的时候做错了，想来想去觉得答案选错了，后来才发现是我把属性与元组的概念弄反.
等值连接和自然连接的区别，三种连接的辨析：非等值连接，等值连接，自然连接
热门推荐
Light
03-22 3万+
表P：A B 1 2 2 1 表 S： A B R：B C 1 2 2 1 3 3 7 2 5 9 ...
数据库：笛卡儿积、连接、等值连接、自然连接、外连接、嵌套循环连接、排序合并连接、索引连接和哈希连接
weixin_43992162的博客
06-04 6830
写在前面 连接是数据库算法的一个重要内容，但数据库的知识有些忘了，最近刚好需要，就又看着笔记重新整理了一遍。
数据库-等值连接与自然连接的区别
trytounderstand的博客
02-11 1万+
1、自然连接一定是等值连接，但等值连接不一定是自然连接。 2、等值连接要求相等的分量，不一定是公共属性；而自然连接要求相等的分量必须是公共属性。 3、等值连接不把重复的属性除去；而自然连接要把重复的属性除去。
mysql 左连接、右连接和内连接
09-11
以MySql为例。在MySQL数据库中建立两张数据表，并分别插入一些数据。
MYSQL 左连接右连接和内连接的详解及区别
09-09
主要介绍了MYSQL 左连接右连接和内连接的详解及区别的相关资料,需要的朋友可以参考下
mysql的左右内连接用法实例
12-15
用个例子来解析下mysql的左连接， 右连接和内连接 代码如下:create table user_id ( id decimal(18) ); create table user_profile ( id decimal(18) , name varchar(255) ) ; insert into user_id values ...
MySQL表LEFT JOIN左连接与RIGHT JOIN右连接的实例教程
09-10
主要介绍了MySQL表LEFT JOIN左连接与RIGHT JOIN右连接的实例教程,表连接操作是MySQL入门学习中的基础知识,需要的朋友可以参考下
mysql连接查询（左连接，右连接，内连接）
12-16
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。 LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。 RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录...
MySQL 8.0.18 Hash Join不支持left/right join左右连接问题
09-09
在MySQL 8.0.18中，增加了Hash Join新功能，它适用于未创建索引的字段，做等值关联查询。这篇文章给大家介绍MySQL 8.0.18 Hash Join不支持left/right join左右连接，感兴趣的朋友一起看看吧
剖析MySQL左连接右连接等值连接异同.pdf
09-30
剖析MySQL左连接右连接等值连接异同.pdf
mysql sql99语法 内连接非等值连接详解
09-09
在本篇文章里小编给大家整理的是一篇关于mysql sql99语法 内连接非等值连接的相关知识点文章，有需要的朋友们可以学习下。
JDBC：Java连接MySQL数据库（优化版）
12-14
Java连接MySQL数据库（优化版） 目的： 当需要把应用程序从开发环境移植到生产环境时，不用修改源代码，只需要修改mysql.ini配置文件 方法： 不直接把数据库连接信息..."jdbc:mysql://127.0.0.1:3306/dome" jdbc:mysq
mysql 左连接 右连接
最新发布
09-07
MySQL中的左连接和右连接是用来合并两个或多个表中的数据的查询操作。 左连接（LEFT JOIN）：将左表中的所有记录都包括在结果集中，而右表中没有匹配的记录则用NULL值填充。语法如下： ```sql SELECT 列名 FROM 左表名 LEFT JOIN 右表名 ON 连接条件; ``` 例如，假设有两个表A和B，我们希望查询A表中的所有记录，并且将与B表中匹配的记录一同返回，如果没有匹配的记录，则B表相关列的值为NULL。可以使用以下语句： ```sql SELECT * FROM A LEFT JOIN B ON A.id = B.id; ``` 右连接（RIGHT JOIN）：与左连接相反，将右表中的所有记录都包括在结果集中，而左表中没有匹配的记录则用NULL值填充。语法如下： ```sql SELECT 列名 FROM 左表名 RIGHT JOIN 右表名 ON 连接条件; ``` 例如，使用右连接查询A表和B表的数据，可以使用以下语句： ```sql SELECT * FROM A RIGHT JOIN B ON A.id = B.id; ``` 需要注意的是，LEFT JOIN和RIGHT JOIN只是连接操作的方向不同，最终结果集合是一样的，只是列的顺序不同。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值