left join on 和 where

来源：互联网发布：卡乐光电 led 软件编辑：程序博客网时间：2024/06/08 11:11

本例中表结构存在不合理地方，只为验证where作用域问题。

1、建表

DROP TABLE IF EXISTS `orders`;CREATE TABLE `orders` (  `fid` bigint(20) NOT NULL,  `fnumber` varchar(20) DEFAULT NULL,  `fprice` double(10,2) DEFAULT NULL,  `fcustomerid` bigint(20) DEFAULT NULL,  `ftypeid` bigint(20) DEFAULT NULL,  PRIMARY KEY (`fid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of orders-- ----------------------------INSERT INTO `orders` VALUES ('1', '1', '1.00', '1', '1');INSERT INTO `orders` VALUES ('2', '2', '2.00', '2', '2');INSERT INTO `orders` VALUES ('3', '3', '3.00', '3', '3');INSERT INTO `orders` VALUES ('4', '4', '4.00', '4', '4');-- ------------------------------ Table structure for `user`-- ----------------------------DROP TABLE IF EXISTS `user`;CREATE TABLE `user` (  `id` bigint(32) NOT NULL AUTO_INCREMENT,  `name` varchar(20) DEFAULT NULL,  `age` varchar(3) DEFAULT NULL,  `fid` bigint(32) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;-- ------------------------------ Records of user-- ----------------------------INSERT INTO `user` VALUES ('1', 'tom', '4', '1');INSERT INTO `user` VALUES ('2', 'jock', '2', null);INSERT INTO `user` VALUES ('3', 'lily', '4', null);

2、表结构

user_table

orders_table

3、查询语句

select * from why.orders o left join why.user u on o.fid = u.fid ;select * from why.orders o left join why.user u on o.fid = u.fid where u.fid is null;select * from why.orders o left join why.user u on o.fid = u.fid and u.fid is  null;

4、结果（按上面查询语句顺序）

5、结论

从上面查询语句和结果看出，where u.fid is null 是对整个结果集的过滤，而on u.fid is null为join过滤条件。

0 0