The difference between Mysql's left connection and right connection

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