MySQL join left join right join 區(qū)別
前提準備條件
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
左右表傻傻分不清楚的仔細閱讀就可以了婉称。
