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
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
左右表傻傻分不清楚的仔细阅读就可以了。
