Mysql的左连接与右连接区别_MySQL
Jun 01, 2016 pm 01:01 PMMysql 的左连接与右连接区别
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 |

Hot Article

Hot tools Tags

Hot Article

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Reduce the use of MySQL memory in Docker

How do you alter a table in MySQL using the ALTER TABLE statement?

How to solve the problem of mysql cannot open shared library

What is SQLite? Comprehensive overview

Run MySQl in Linux (with/without podman container with phpmyadmin)

Running multiple MySQL versions on MacOS: A step-by-step guide

How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)?

How do I configure SSL/TLS encryption for MySQL connections?
