作者：gengjiang3_946 | 来源：互联网 | 2018-04-18 04:44
Mysql的左连接与右连接区别1SQLLEFTJOIN关键字LEFTJOIN关键字会从左表(table_name1)那里返回所有的行，即使在右表(table_name2)中没有匹配的行。2SQLRIGHT
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(&＃39;1&＃39;,&＃39;Java R&D&＃39;,&＃39;Java R&D&＃39;);
insert into `department` (`ID`, `CODE`, `NAME`) values(&＃39;2&＃39;,&＃39;Mysql R&D&＃39;,&＃39;Mysql R&D&＃39;);
insert into `department` (`ID`, `CODE`, `NAME`) values(&＃39;3&＃39;,&＃39;Interface R&D&＃39;,&＃39;Interface R&D&＃39;);
insert into `department` (`ID`, `CODE`, `NAME`) values(&＃39;4&＃39;,&＃39;Android R&D&＃39;,&＃39;Android R&D&＃39;);
insert into `department` (`ID`, `CODE`, `NAME`) values(&＃39;5&＃39;,&＃39;IOS R&D&＃39;,&＃39;IOS R&D&＃39;);
insert into `Job` (`id`, `FirstName`, `LastName`, `LoginName`, `dep_id`) values(&＃39;1&＃39;,&＃39;Peter&＃39;,&＃39;Qiu&＃39;,&＃39;Qiu&＃39;,&＃39;1&＃39;);
insert into `Job` (`id`, `FirstName`, `LastName`, `LoginName`, `dep_id`) values(&＃39;2&＃39;,&＃39;Tom&＃39;,&＃39;Lee&＃39;,&＃39;Lee&＃39;,&＃39;2&＃39;);
insert into `Job` (`id`, `FirstName`, `LastName`, `LoginName`, `dep_id`) values(&＃39;3&＃39;,&＃39;Garfield&＃39;,&＃39;Wang&＃39;,&＃39;Wang&＃39;,&＃39;3&＃39;);
insert into `Job` (`id`, `FirstName`, `LastName`, `LoginName`, `dep_id`) values(&＃39;4&＃39;,&＃39;哆啦A梦&＃39;,&＃39;Jone&＃39;,&＃39;Jone&＃39;,NULL);
insert into `Job` (`id`, `FirstName`, `LastName`, `LoginName`, `dep_id`) values(&＃39;5&＃39;,&＃39;Westlife&＃39;,&＃39;西城男孩&＃39;,&＃39;Westlife&＃39;,&＃39;8&＃39;);
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 |
京公网安备 11010802041100号