运行平台:Mysql
目的:比较join......on 后面的and 和where的区别
1)建表
创建A表
- SET FOREIGN_KEY_CHECKS=0;
- -- ----------------------------
- -- Table structure for `a`
- -- ----------------------------
- DROP TABLE IF EXISTS `a`;
- CREATE TABLE `a` (
- `id` decimal(10,0) NOT NULL DEFAULT '0',
- `link` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
- `a_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
- -- ----------------------------
- -- Records of a
- -- ----------------------------
- INSERT INTO `a` VALUES ('1', '1', 'jack');
- INSERT INTO `a` VALUES ('2', '1', 'jack');
- INSERT INTO `a` VALUES ('3', '1', 'leo');
创建B表
- SET FOREIGN_KEY_CHECKS=0;
- -- ----------------------------
- -- Table structure for `b`
- -- ----------------------------
- DROP TABLE IF EXISTS `b`;
- CREATE TABLE `b` (
- `id` decimal(10,0) NOT NULL DEFAULT '0',
- `link` varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL,
- `b_name` varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
- -- ----------------------------
- -- Records of b
- -- ----------------------------
- INSERT INTO `b` VALUES ('1', '1', 'frowna');
- INSERT INTO `b` VALUES ('2', '1', 'frowna');
- INSERT INTO `b` VALUES ('3', '1', 'kiki');
2)比较下面的运行结果
- select * from A left join B on A.link =B.link where A.a_name='jack'
- 和
- select * from A left join B on A.link =B.link and A.a_name='jack'
- select * from A inner join B on A.link =B.link where A.a_name='jack'
- 和
- select * from A inner join B on A.link =B.link and A.a_name='jack'
- select * from A right join B on A.link =B.link where A.a_name='jack'
- 和
- select * from A right join B on A.link =B.link and A.a_name='jack'
- select * from A right join B on A.link =B.link where A.a_name is null
- 和
- select * from A right join B on A.link =B.link and A.a_name is null
3)结论
3.1) where 是在两个表join完成后,再附上where条件。
即
- select * from (select A.a_name,B.b_name from A left join B on A.link =B.link)t where t.a_name='jack';
- 等价为
- select A.a_name,B.b_name from A left join B on A.link =B.link where A.a_name='jack'
3.2)
而 and 则是在表连接前过滤A表或B表里面哪些记录符合连接条件,同时会兼顾是left join还是right join。即
假如是左连接的话,如果左边表的某条记录不符合连接条件,那么它不进行连接,但是仍然留在结果集中(此时右边部分的连接结果为NULL)。
3.3)建议尽量用where来过滤条件,以避免复杂的逻辑考虑。(除非在某些情况下(后接其他sql语句),用and会报错,才用and,但要考虑是否影响正确结果。)