Mysql的左连接与右连接区别 - mysql数据库栏目 - 自

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

查看更多关于Mysql的左连接与右连接区别 - mysql数据库栏目 - 自的详细内容...