对比测试
基准测试表中将可能看到的数据(编号：SQL_1)
root@localhost : eugene 03:25:07> SELECT M.ID,M.username FROM left_table M WHERE M.ID<=6;+----+-----------------+| 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)
root@localhost : eugene 03:37:58> 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 | 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)
root@localhost : eugene 03:40:20> 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 | 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)
root@localhost : eugene 03:41:27> 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 | 06440350@qq.com | 817 | 39 || 6 | 02026078@qq.com | 5177 | 36 | +----+-----------------+------------+--------+对应编号：SQL_3的等值连接测试的SQL及数据(编号：SQL_5)
root@localhost : eugene 03:42:53> 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 |+----+-----------------+------------+--------+| 1 | 06440350@qq.com | 817 | 39 | +----+-----------------+------------+--------+不带JOIN关键字的等值连接
对应编号：SQL_4的等值连接测试的SQL及数据(编号：SQL_6)
root@localhost : eugene 03:43:01> 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 | 06440350@qq.com | 817 | 39 || 6 | 02026078@qq.com | 5177 | 36 | +----+-----------------+------------+--------+对应编号：SQL_5的等值连接测试的SQL及数据(编号：SQL_7)
root@localhost : eugene 03:49:35> 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 |+----+-----------------+------------+--------+| 1 | 06440350@qq.com | 817 | 39 | +----+-----------------+------------+--------+常见错误用法的左连接
错把限制表right_table的条件，从ON 子句中放到WHERE字句中的SQL及数据(编号：SQL_8)
root@localhost : eugene 03:49:57> 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 AND N.SearchNum>300;+----+-----------------+------------+--------+| ID | username | CollectNum | BuyNum |+----+-----------------+------------+--------+| 1 | 06440350@qq.com | 817 | 39 | +----+-----------------+------------+--------+错把限制表left_table或称影响最终记录集的条件，从WHERE子句中放到ON字句中的SQL及数据(编号：SQL_9)
root@localhost : eugene 03:54:14> SELECT M.ID,M.username,N.CollectNum,N.BuyNum-> 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 | +----+-----------------+------------+--------+
