MySQL join left join right join 区别

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

right Join

与右连接查询的意义相反

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

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