MySQL join left join right join 区别

2017-10-21 14:16:07来源:oschina作者:superduper人点击

分享
MySQL join left join right join 区别
前提准备条件
Table User
Table Book#Table User
CREATE TABLE `user` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`pwd` varchar(255) DEFAULT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
# Table Book
CREATE TABLE `book` (
`bookId` int(11) NOT NULL AUTO_INCREMENT,
`bookName` varchar(255) DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
PRIMARY KEY (`bookId`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8#Insert User
INSERT INTO `user` VALUES ('1', 'a', 'ap');
INSERT INTO `user` VALUES ('2', 'b', 'bp');
INSERT INTO `user` VALUES ('3', 'c', 'cp');
INSERT INTO `user` VALUES ('4', 'd', 'dp');
INSERT INTO `user` VALUES ('5', 'e', 'ep');
INSERT INTO `user` VALUES ('6', 'f', 'fp');
# Insert Book
INSERT INTO `book` VALUES ('1', 'Java', '1');
INSERT INTO `book` VALUES ('2', 'JavaScript', '2');
INSERT INTO `book` VALUES ('3', 'Ruby', '3');
INSERT INTO `book` VALUES ('4', 'Python', '4');
INSERT INTO `book` VALUES ('5', 'Rust', '5');
INSERT INTO `book` VALUES ('6', 'Go', '2');
INSERT INTO `book` VALUES ('7', 'R', '1');
INSERT INTO `book` VALUES ('8', 'C', '4');
INSERT INTO `book` VALUES ('9', 'C++', '3');
INSERT INTO `book` VALUES ('10', 'C#', '5');
join

指User和Book中都存在的关联关系的结果


SELECT u.*,book.* from `user` u
join book ON u.uid = book.uid

image.png<br>


#left Join
左连接查询,根据字面意思可以理解,主要返回左表中的信息,如果在关联关系不匹配的情况下,回尽可能的返回左表中的信息字段,又表为空


SELECT u.*,book.* from `user` u
LEFT JOIN book ON u.uid = book.uid

image.png


#right Join
与右连接查询的意义相反


SELECT * from book b
RIGHT JOIN `user` u ON u.uid = b.uid

image.png


左右表傻傻分不清楚的仔细阅读就可以了。

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台