The difference between Mysql's left connection and right connection
1.SQL left JOIN keyword
The left JOIN keyword returns all rows from the table (TABLE_NAME1), even if there are no matching rows in the right table (table_name2).
2.SQL Right JOIN keyword
The right JOIN keyword returns all rows from the table (table_name2), even if there are no matching rows in the left table (table_name1).
3. Create two tables in Devdb in MySQL
Create Table Department |
CREATE TABLE ' Department ' ( ' ID ' int (one) not NULL auto_increment, ' CODE ' varchar (+) DEFAULT NULL, ' NAME ' varchar ($) DEFAULT NULL, PRIMARY KEY (' ID ') ) Engine=innodb auto_increment=5 DEFAULT Charset=utf8 |
Create Table Job |
CREATE TABLE ' job ' ( ' id ' int (one) not NULL auto_increment, ' FirstName ' varchar (+) DEFAULT NULL, ' LastName ' varchar (+) DEFAULT NULL, ' LoginName ' varchar (+) DEFAULT NULL, ' dep_id ' int (one) DEFAULT NULL, PRIMARY KEY (' id ') ) Engine=innodb auto_increment=5 DEFAULT Charset=utf8 |
4. Inserting data into the department table and the job table
insert into ' Department ' (' ID ', ' Code ', ' name ') VALUES (' 1 ', ' Java R/R ', ' Java R & R ') INSERT INTO ' Department ' (' ID ', ' CODE ', ' NAME ') VALUES (' 2 ', ' MySQL R/R ', ' MySQL R & R '); INSERT INTO ' Department ' (' I D ', ' Code ', ' name ') VALUES (' 3 ', ' Interface R ', ' Interface R '); INSERT INTO ' Department ' (' ID ', ' Code ', ' name ') v Alues (' 4 ', ' Android R/R ', ' Android R/R '); INSERT INTO ' Department ' (' ID ', ' CODE ', ' NAME ') VALUES (' 5 ', ' IOS R & R ') , ' IOS R & R ');
Insert INTO ' Job ' (' id ', ' FirstName ', ' LastName ', ' LoginName ', ' dep_id ') VALUES (' 1 ', ' Peter ', ' Qiu ', ' Qiu ', ' 1 '); insert Into ' job ' (' IDs ', ' 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 ', ' Doraemon ', ' jone ', ' Jone ', NULL); insert INTO ' Job ' (' id ', ' FirstName ', ' LastName ', ' LoginName ', ' dep_id ') VALUES (' 5 ', ' Westlife ', ' Xicheng Boys ', ' Westlife ', ' 8 ');
Department table data
Id |
CODE |
NAME |
1 |
Java R/R |
Java R/R |
2 |
Mysql R & R |
Mysql R & R |
3 |
Interface |
Interface |
4 |
Android R/R |
Android R/R |
5 |
IOS |
IOS |
Job table Data
Id |
FirstName |
LastName |
LoginName |
dep_id |
1 |
Peter |
Qiu |
Qiu |
1 |
2 |
Tom |
Lee |
Lee |
2 |
3 |
Garfield |
Wang |
Wang |
3 |
4 |
Doraemon |
Jone |
Jone |
(NULL) |
5 |
Westlife |
West Side Boy |
Westlife |
8 |
5. Left Join with Job table
/* Use the job table to connect to the left, at which point the data is mainly based on the job table, associated with the department table, if the job's dep_id is null or there is no corresponding ID match in the Department table corresponding fields are automatically populated Null*/select * from Job Table_name1 left joins department table_name2 on Table_name1. ' dep_id ' = table_name2. ' ID ';
The data that was found
Id |
FirstName |
LastName |
LoginName |
dep_id |
Id |
CODE |
NAME |
1 |
Peter |
Qiu |
Qiu |
1 |
1 |
Java R/R |
Java R/R |
2 |
Tom |
Lee |
Lee |
2 |
2 |
Mysql R & R |
Mysql R & R |
3 |
Garfield |
Wang |
Wang |
3 |
3 |
Interface |
Interface |
4 |
Doraemon |
Jone |
Jone |
(NULL) |
(NULL) |
(NULL) |
(NULL) |
5 |
Westlife |
West Side Boy |
Westlife |
8 |
(NULL) |
(NULL) |
(NULL) |
6. Right join on the job table
/* Use the job table to connect right, at this time the data mainly in the department table, the associated job table, if the job dep_id is null or no corresponding ID match in the Department table corresponding fields are automatically populated Null*/select * from Job Table_name1 Right JOIN department table_name2 on table_name1. ' dep_id ' = table_name2. ' ID ';
The data that was found
Id |
FirstName |
LastName |
LoginName |
dep_id |
Id |
CODE |
NAME |
1 |
Peter |
Qiu |
Qiu |
1 |
1 |
Java R/R |
Java R/R |
2 |
Tom |
Lee |
Lee |
2 |
2 |
Mysql R & R |
Mysql R & R |
3 |
Garfield |
Wang |
Wang |
3 |
3 |
Interface |
Interface |
(NULL) |
(NULL) |
(NULL) |
(NULL) |
(NULL) |
4 |
Android R/R |
Android R/R |
(NULL) |
(NULL) |
(NULL) |
(NULL) |
(NULL) |
5 |
IOS |
IOS |
Reprint Please specify: http://blog.csdn.net/qiuzhping/article/details/41623967
The difference between Mysql's left connection and right connection