Difference between left and right connections of Mysql

Difference between left and right connections of Mysql
Difference between left join and right JOIN in Mysql 1. SQL LEFT JOIN keyword

The left join keyword returns all rows from the LEFT table (table_name1), even if no matching row exists in the right table (table_name2.

2. SQL RIGHT JOIN keyword

The right join keyword returns all rows from the RIGHT table (table_name2), even if no matching row exists in the left table (table_name1.

3. Create two tables in DevDB of Mysql

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. insert data in the department table and Job table

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', 'field', '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 & 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 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	Xicheng boy	Westlife	8

5. left join in the Job table)

/* Perform left join on the Job table. In this case, the data is mainly the Job table and associated with the department table, if the dep_id of the Job is null or no corresponding Id matching field in the department table is automatically filled in null */SELECT * FROM Job table_name1 left join department table_name2 ON table_name1. 'dep_id' = table_name2. 'id ';

Data found

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	Doraemon	Jone	Jone	(NULL)	(NULL)	(NULL)	(NULL)
5	Westlife	Xicheng boy	Westlife	8	(NULL)	(NULL)	(NULL)

6. right join in the Job table)

/* Right join with the Job table. In this case, the data is mainly in the department table and associated with the Job table, if the dep_id of the Job is null or no corresponding Id matches the corresponding field in the department table, the Job automatically fills in null */SELECT * FROM Job table_name1 right join department table_name2 ON table_name1. 'dep_id' = table_name2. 'id ';

Data found

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