MySQL join left join right join 區(qū)別

前提準(zhǔn)備條件

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中都存在的關(guān)聯(lián)關(guān)系的結(jié)果

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

image.png

left Join

左連接查詢，根據(jù)字面意思可以理解，主要返回左表中的信息，如果在關(guān)聯(lián)關(guān)系不匹配的情況下，回盡可能的返回左表中的信息字段，又表為空

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

左右表傻傻分不清楚的仔細(xì)閱讀就可以了。