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

轉載請注明：http://blog.csdn.net/qiuzhping/article/details/41623967