从业以来,发现很多技术人员和同事向笔者或是在网络上咨询过LEFT JOIN 如何写及其特性,而等值连接一般都没问题,几年前曾经写过一份Microsoft SQL Server版本的,此次特意再写一份MySQL版本的,希望阅读过的技术人员,可以起到解惑功效。
1、三种连接的语法
为便于更多的技友快速读懂、理解,我们只讨论2张表对象进行连接操作的情况,大于2张表对象进行的连接操作原理也是一样的。
左连接(LEFT JOIN )
FROM left_table M LEFT JOIN right_table N ON M. columnname_join=N. columnname_join AND N. columnname=XXX
WHERE M.columnname=XXX…..
右连接(RIGHT JOIN)
FROM left_table M RIGHT JOIN right_table N ON M. columnname_join=N. columnname_join AND M. columnname=XXX
WHERE N.columnname=XXX…..
等值连接
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….
备注:注意上面SQL语法中加粗的红色字体部分,三种不同连接语法的变化。
三种连接的特性
左连接(LEFT JOIN )
ON字句连接条件,用于把2表中等值的记录连接在一起,但是不影响记录集的数量。若是表left_table中的某记录,无法在表right_table找到对应的记录,则此记录依然显示在记录集钟,只表 right_table需要在查询显示的列的值用NULL替代;
ON字句连接条件中表right_table. columnname=XXX用于控制right_table表是否有符合要求的列值还是用NULL替换的方式显示在查询列中,不影响记录集的数量;
WHERE字句控制记录是否符合查询要求,不符合则过滤掉;
总结:ON字句控制right_table的列值符合显示,还是不符合就用NULL替换,不影响最终符合查询要求的记录集;WHERE字句是控制那些记录是显示在最终的记录集中。
右连接(RIGHT JOIN)
ON字句连接条件,用于把2表中等值的记录连接在一起,若是表right_table中的某记录,无法在表left_table找到对应的记录,则表 left_able需要在查询显示的列的值用NULL替代;
ON字句连接条件中表left_table. columnname=XXX用于控制left_table表是否有符合要求的列值,还是用NULL替换的方式显示在查询列表中;
WHERE字句控制记录是否符合查询要求,不符合则过滤掉;
总结:ON字句控制left_table的列值符合显示,还是不符合而用NULL替换掉,不影响最终符合查询要求的记录集;WHERE字句是控制那些记录是显示在最终的记录集中。我们会发现LEFT JOIN 和RIGHT JOIN是类似的,只是以连接关键字左边还是右边表为准匹配。
等值连接
ON 字句连接条件,不再与左连接或右连接的功效一样,除了作为2表记录匹配的条件外,还会起到过滤记录的作用,若left_table中记录无法在right_table中找到对应的记录,则会被过滤掉;
WHERE字句,不管是涉及表left_table、表right_table上的限制条件,还是涉及2表连接的条件,都会对记录集起到过滤作用,把不符合要求的记录刷选掉;
连接语法的测试环境
测试用表结构
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 ' ;
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 ' ;
用于测试的数据
重复执行10次INSERT*** VALUE ***语句:
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));
执行一次INSERT ** SELECT **语句:
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 ;
注释:表left_table将会有ID值1,2,3,***,10连续的记录10条,表right_table中有ID值1,6离散的记录2条,并且2表关联条件为:left_table.ID=right_table.UID实现。
对比测试
基准测试表中将可能看到的数据(编号:SQL_1)
+----+-----------------+
| ID | username |
+----+-----------------+
| 1 | 06440350 @qq.com |
| 2 | 25173782 @qq.com |
| 3 | 66328120 @qq.com |
| 4 | 16752438 @qq.com |
| 5 | 92117196 @qq.com |
| 6 | 02026078 @qq.com |
+----+-----------------+
标准左连接
ON字句中无连接字段之外条件的SQL及数据(编号:SQL_2)
-> FROM left_table M LEFT JOIN right_table N ON M.ID = N.UID
-> WHERE M.ID <= 6 ;
+----+-----------------+------------+--------+
| ID | username | CollectNum | BuyNum |
+----+-----------------+------------+--------+
| 1 | 06440350 @qq.com | 817 | 39 |
| 2 | 25173782 @qq.com | NULL | NULL |
| 3 | 66328120 @qq.com | NULL | NULL |
| 4 | 16752438 @qq.com | NULL | NULL |
| 5 | 92117196 @qq.com | NULL | NULL |
| 6 | 02026078 @qq.com | 5177 | 36 |
+----+-----------------+------------+--------+
ON字句中除连接字段条件之外,还有right_table限制条件的SQL及数据(编号:SQL_3)
-> 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 | 06440350 @qq.com | 817 | 39 |
| 2 | 25173782 @qq.com | NULL | NULL |
| 3 | 66328120 @qq.com | NULL | NULL |
| 4 | 16752438 @qq.com | NULL | NULL |
| 5 | 92117196 @qq.com | NULL | NULL |
| 6 | 02026078 @qq.com | NULL | NULL |
+----+-----------------+------------+--------+
带JOIN关键字的等值连接
对应编号:SQL_2的等值连接测试的SQL及数据(编号:SQL_4)
-> FROM left_table M INNER JOIN right_table N ON M.ID = N.UID
-> WHERE M.ID <= 6 ;
+----+-----------------+------------+--------+
| ID | username | CollectNum | BuyNum |
+----+-----------------+------------+--------+
| 1 | 06440350 @qq.com | 817 | 39 |
| 6 | 02026078 @qq.com | 5177 | 36 |
+----+-----------------+------------+--------+
对应编号:SQL_3的等值连接测试的SQL及数据(编号:SQL_5)
-> 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 |
+----+-----------------+------------+--------+
| 1 | 06440350 @qq.com | 817 | 39 |
+----+-----------------+------------+--------+
不带JOIN关键字的等值连接
对应编号:SQL_4的等值连接测试的SQL及数据(编号:SQL_6)
-> FROM left_table M,right_table N
-> WHERE M.ID = N.UID AND M.ID <= 6 ;
+----+-----------------+------------+--------+
| ID | username | CollectNum | BuyNum |
+----+-----------------+------------+--------+
| 1 | 06440350 @qq.com | 817 | 39 |
| 6 | 02026078 @qq.com | 5177 | 36 |
+----+-----------------+------------+--------+
对应编号:SQL_5的等值连接测试的SQL及数据(编号:SQL_7)
-> FROM left_table M,right_table N
-> WHERE M.ID = N.UID AND M.ID <= 6 AND N.SearchNum > 300 ;
+----+-----------------+------------+--------+
| ID | username | CollectNum | BuyNum |
+----+-----------------+------------+--------+
| 1 | 06440350 @qq.com | 817 | 39 |
+----+-----------------+------------+--------+
常见错误用法的左连接
错把限制表right_table的条件,从ON 子句中放到WHERE字句中的SQL及数据(编号:SQL_8)
-> FROM left_table M LEFT JOIN right_table N ON M.ID = N.UID
-> WHERE M.ID <= 6 AND N.SearchNum > 300 ;
+----+-----------------+------------+--------+
| ID | username | CollectNum | BuyNum |
+----+-----------------+------------+--------+
| 1 | 06440350 @qq.com | 817 | 39 |
+----+-----------------+------------+--------+
错把限制表left_table或称影响最终记录集的条件,从WHERE子句中放到ON字句中的SQL及数据(编号:SQL_9)
-> FROM left_table M LEFT JOIN right_table N ON M.ID = N.UID AND M.ID <= 6 AND N.SearchNum > 300 ;
+----+-----------------+------------+--------+
| ID | username | CollectNum | BuyNum |
+----+-----------------+------------+--------+
| 6 | 02026078 @qq.com | NULL | NULL |
| 7 | 03990516 @qq.com | NULL | NULL |
| 9 | 05301926 @qq.com | NULL | NULL |
| 1 | 06440350 @qq.com | 817 | 39 |
| 4 | 16752438 @qq.com | NULL | NULL |
| 2 | 25173782 @qq.com | NULL | NULL |
| 10 | 56599949 @qq.com | NULL | NULL |
| 3 | 66328120 @qq.com | NULL | NULL |
| 5 | 92117196 @qq.com | NULL | NULL |
| 8 | 93677961 @qq.com | NULL | NULL |
+----+-----------------+------------+--------+
测试总结
编号SQL_1:表left_table真实参与测试的记录的ID值:1,2,3,4,5,6共计6条连续的记录;
编号SQL_2、SQL_3:对于LEFT JOIN 连接操作,增加right_table的限制条件,不影响最后记录集的数量,但是影响right_table表中需要显示出来的列值,不符合限制条件的情况下,则用NULL值替代;
编号SQL_4、SQL_5:对于等值连接操作,ON字句中增加对right_table的限制条件,会对最终的记录集数量产生影响;
编号SQL_6、SQL_7:对于等值连接操作,WHERE字句中增加对right_table的限制条件,会对最终的记录集数量产生影响;
编号(SQL_2、SQL_4)或(SQL_3、SQL_5):我们可以轻易发现编号:SQL_2、SQL_4 之间唯一的差别是连接的类型不同:SQL_2为左连接,而SQL_4为等值连接,其他所有的都一样,然后SQL执行后获得的记录集却截然不一样,并且SQL_2是对符合表left_table限制条件的记录都作为最后的记录集,而SQL_4则是对同时符合表left_table、表right_table限制条件的记录才作为最后的记录集;SQL_2与SQL_4之间对比获得的结论,同样适用于SQL_3与SQL_5之间,为此部再单独描述;
编号(SQL_4、SQL_6)或(SQL_5、SQL_7):对于等值连接操作,不管对right_table的限制条件,放ON字句,还是WHERE子句中,对记录集影响都是一样的;
编号SQL_3、SQL_5、SQL_8:SQL_5与SQL_3相比较我们可发现,用的连接关键字都是:左连接,但是ON子句中的表right_table限制条件被放到WHERE子句中,导致最后获得的记录集完全不一样,而是跟等值连接SQL_8的记录集一样,通过比较可以发现SQL_5的记录集不符合左连接的特性,而是符合等值连接的特性,所以此写法不是左连接语句,而是相当于等值连接的语句;
编号SQL_2、SQL_3、SQL_9:SQl_9与SQL_2、SQL_3的连接关键字都是:左连接,其记录集相比较可以发现限制表left_table的记录条件:M.ID<=6没有起作用,限制表right_table的记录条件:N.SearchNum>300依然有效果,从左连接的特性角度分析,依然属于左连接,只是写法很混乱,容易误导SQL的读者。
--------------------------------------------------------------------------------------------------------
表A记录如下:
aID aNum
1 a20050111
2 a20050112
3 a20050113
4 a20050114
5 a20050115
表B记录如下:
bID bName
1 2006032401
2 2006032402
3 2006032403
4 2006032404
8 2006032408
实验如下:
1. left join
sql语句如下:
SELECT * FROM A LEFT JOIN B ON A.aID = B.bID
结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
5 a20050115 NULL NULL
(所影响的行数为 5 行)
结果说明:
left join是以A表的记录为基础的,A可以看成左表, B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL.
2. right join
sql语句如下:
SELECT * FROM A RIGHT JOIN B ON A.aID = B.bID
结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
NULL NULL 8 2006032408
(所影响的行数为 5 行)
结果说明:
仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.
3.inner join
sql语句如下:
SELECT * FROM A INNERJOIN B ON A.aID = B.bID
结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
结果说明:
很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.
-----------------[以下为网上的一点资料]------------------
LEFT JOIN操作用于在任何的 FROM 子句中,组合来源表的记录。使用 LEFT JOIN 运算来创建一个左边外部联接。左边外部联接将包含了从第一个(左边)开始的两个表中的全部记录,即使在第二个(右边)表中并没有相符值的记录。
语法:
FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2
说明:
① table1, table2参数用于指定要将记录组合的表的名称。
② field1, field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据,但它们不需要有相同的名称。
③ compopr参数指定关系比较运算符:"=", "<", ">", "<=", ">=" 或 "<>"。
④ 如果在INNER JOIN操作中要联接包含Memo 数据类型或 OLE Object 数据类型数据的字段,将会发生错误。