Mysql 的左连接与右连接区别1.SQL LEFT JOIN 关键字LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。 2.SQL RIGHT JOIN 关键字RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。 3.在Mysql中的DevDB中创建两个表 | Create Table department |
CREATE TABLE `department` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`CODE` varchar(100) DEFAULT NULL,
`NAME` varchar(200) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
| Create Table job |
CREATE TABLE `job` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`FirstName` varchar(32) DEFAULT NULL,
`LastName` varchar(32) DEFAULT NULL,
`LoginName` varchar(32) DEFAULT NULL,
`dep_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 | 4.在department表和Job表中插入数据insert into `department` (`ID`, `CODE`, `NAME`) values('1','Java R&D','Java R&D');insert into `department` (`ID`, `CODE`, `NAME`) values('2','Mysql R&D','Mysql R&D');insert into `department` (`ID`, `CODE`, `NAME`) values('3','Interface R&D','Interface R&D');insert into `department` (`ID`, `CODE`, `NAME`) values('4','Android R&D','Android R&D');insert into `department` (`ID`, `CODE`, `NAME`) values('5','IOS R&D','IOS R&D');insert into `Job` (`id`, `FirstName`, `LastName`, `LoginName`, `dep_id`) values('1','Peter','Qiu','Qiu','1');insert into `Job` (`id`, `FirstName`, `LastName`, `LoginName`, `dep_id`) values('2','Tom','Lee','Lee','2');insert into `Job` (`id`, `FirstName`, `LastName`, `LoginName`, `dep_id`) values('3','Garfield','Wang','Wang','3');insert into `Job` (`id`, `FirstName`, `LastName`, `LoginName`, `dep_id`) values('4','哆啦A梦','Jone','Jone',NULL);insert into `Job` (`id`, `FirstName`, `LastName`, `LoginName`, `dep_id`) values('5','Westlife','西城男孩','Westlife','8');department表数据 | ID | CODE | NAME | | 1 | Java R&D | Java R&D | | 2 | Mysql R&D | Mysql R&D | | 3 | Interface R&D | Interface R&D | | 4 | Android R&D | Android R&D | | 5 | IOS R&D | IOS R&D | Job表数据 | id | FirstName | LastName | LoginName | dep_id | | 1 | Peter | Qiu | Qiu | 1 | | 2 | Tom | Lee | Lee | 2 | | 3 | Garfield | Wang | Wang | 3 | | 4 | 哆啦A梦 | Jone | Jone | (NULL) | | 5 | Westlife | 西城男孩 | Westlife | 8 | 5.以Job表进行左连接(left join)/*以Job表进行左连接，此时数据主要以Job表为主，关联department表，如果Job的dep_id为null或者在department表中无对应的Id匹配对应的字段自动填充null*/SELECT * FROM Job table_name1 LEFT JOIN department table_name2 ON table_name1.`dep_id`= table_name2.`ID`; 查出来的数据 | id | FirstName | LastName | LoginName | dep_id | ID | CODE | NAME | | 1 | Peter | Qiu | Qiu | 1 | 1 | Java R&D | Java R&D | | 2 | Tom | Lee | Lee | 2 | 2 | Mysql R&D | Mysql R&D | | 3 | Garfield | Wang | Wang | 3 | 3 | Interface R&D | Interface R&D | | 4 | 哆啦A梦 | Jone | Jone | (NULL) | (NULL) | (NULL) | (NULL) | | 5 | Westlife | 西城男孩 | Westlife | 8 | (NULL) | (NULL) | (NULL) |
6.以Job表进行右连接(right join)/*以Job表进行右连接，此时数据主要以department表为主，关联Job表，如果Job的dep_id为null或者在department表中无对应的Id匹配对应的字段自动填充null*/SELECT * FROM Job table_name1 RIGHT JOIN department table_name2 ON table_name1.`dep_id`= table_name2.`ID`; 查出来的数据 | id | FirstName | LastName | LoginName | dep_id | ID | CODE | NAME | | 1 | Peter | Qiu | Qiu | 1 | 1 | Java R&D | Java R&D | | 2 | Tom | Lee | Lee | 2 | 2 | Mysql R&D | Mysql R&D | | 3 | Garfield | Wang | Wang | 3 | 3 | Interface R&D | Interface R&D | | (NULL) | (NULL) | (NULL) | (NULL) | (NULL) | 4 | Android R&D | Android R&D | | (NULL) | (NULL) | (NULL) | (NULL) | (NULL) | 5 | IOS R&D | IOS R&D | |