mysql 左连接与右连接的区别吗_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

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网