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
本条技术文章来源于互联网,如果无意侵犯您的权益请点击此处反馈版权投诉
本文系统来源:php中文网